Category: Linq

Queryable Repository

Say we have a Post domain class, with properties like Header, details, and PostDate etc.

We want do many different kind of query/search from task/service layer, but we don’t have to change both task and repository for every new type of search requirement.

It would be nice if PostRepository can take the predict to do query.

        public IQueryable<Post> SearchBy( Expression<Func<Post, bool>> predict)
        {
             return Session.Query<Post>().Where(predict);
        }

For unit test like this:

        [Test]
        public void should_do_search_for_keyword_and_date_range()
        {
            var startDate = new DateTime(2012, 1, 1);
            var endDate = new DateTime(2011, 1, 1);
            var results = _repository.SearchBy(post => post.Header.Contains("asdf") && post.PostDate >= startDate && post.PostDate <= endDate);
            Assert.That(results, Is.Empty);
        }

The generated sql:

NHibernate: select post0_.Post_ID as Post1_0_, post0_.Header as Header0_, post0_.Sub_Header as Sub3_0_, post0_.Description as Descript4_0_, post0_.Is_News_Clip as Is5_0_, post0_.Image_Name as Image6_0_, post0_.More_Url as More7_0_, post0_.Post_Date as Post8_0_, post0_.S_Date as S9_0_, post0_.C_Date as C10_0_, post0_.SU_ID as SU11_0_, post0_.CU_ID as CU12_0_ from Post_TAB post0_ where (post0_.Header like ('%'+@p0+'%')) and post0_.Post_Date>=@p1 and post0_.Post_Date<=@p2;@p0 = 'asdf' [Type: String (4000)], @p1 = 2012-01-01 12:00:00 AM [Type: DateTime (0)], @p2 = 2011-01-01 12:00:00 AM [Type: DateTime (0)]

The the task/service can play criteria in whatever way they want, while the repository won’t care.

In fact, the queryable itself is very flexible, we can just create a Query method in repository class to remove the predict expression from method:

        public IQueryable<Post> Query()
        {
            return Session.Query<Post>();
        }
public IEnumerable<PostedItemDto> FindPostsByCriteria(PostCriteria criteria)
 {
 IQueryable<Post> results = _postRepository.Query();

 if (!(string.IsNullOrEmpty(criteria.Keyword)))
 results = results.Where(post =>
 post.Header.Contains(criteria.Keyword)
 || post.SubHeader.Contains(criteria.Keyword)
 || post.Description.Contains(criteria.Keyword))
 ;

 if ( criteria.FromDate != null ) results = results.Where(post => post.PostDate >= criteria.FromDate);

 if ( criteria.ToDate != null ) results = results.Where(post => post.PostDate <= criteria.ToDate);

 return new PostTranslator().Map(results.ToList());
 }

Unfortunately, QueryOver in new NH can not do this yet. To achieve the same result, repository has to write in this way:

            return Session.QueryOver<Post>()
                .WhereRestrictionOn(post => post.Header).IsLike("%asdk%")
                .WhereRestrictionOn(post => post.PostDate).IsBetween(fromDate).And(endDate)
                .List();

It looks clear, but we have to maintain it for criteria structure changes.

Even LinQ is not strong enough as this post indicated, but it has it’s advantage.

IRepository

I was trying to write a similar post as Mike did. Because I’m currently using LINQ DynamicQuery, so my IRepository looks like this:


    public interface IDynamicQueryable<T> 
    {
        IEnumerable<T> FindAllByCriteria(string criteria);
        IEnumerable<T> FindAllByCriteria(Func<T, bool> criteria);
    }

    // Most simple repository implement this one. 
    public interface IRepository<T>  
    {         
              T FindById(int id);
              T Insert(T data);
              T Update(T newData, T oldData);
              void Delete(T data);
     }

    // Some fancy entities implement this one instead. 
    public interface IDynamicQueryableRepository<T> : IDynamicQueryable<T>, IRepository<T>{}

I spilt the dynamic part from IRepository, just trying not annoy developer create too many never used FindAll() method in their entities. And also I think this is closer to Interface segregation Principle (not really sure). But this indeed gave me flexibility to control the function diversity on different entities, e.g., some entities might never need list-editing/retrieval function.

I do like this LinqDynamicQuery very much and don’t mind to make my repository coupled to Func<T, bool> (what’s this indeed? And what’ the difference than Expression<Func<T, bool>> shown in Fluent NHibernate’s IRepository?)  Here is a good example, if no DynamicQuery:


    public interface ICityRepository : IDynamicQueryableRepository<CityBO>
    {
        IEnumerable<CityBO> FindAllByProvinceId(int provinceId);
        IEnumerable<CityBO> FindAllByCountryId(int countryId);
    }

By using DynamicQuery,


    public class CityListFactory : BusinessListBaseServerFactory<CityBOCollection, CityBO, ICityRepository>, ICityListFactory
    {
        public CityBOCollection Fetch(CountryBO parent)
        {
            // Don't need FindAllByCountryId() any more.
            return Fetch(x => x.CountryId == parent.CountryId);
        }
        public CityBOCollection Fetch(ProvinceBO parent)
        {
            return Fetch(x => x.ProvinceId == parent.ProvinceId);
        }
    }

Linq Left join

Scenario: Search all Stakeholder regardless if they have a current effective address. Thanks to this post.


var qry = from data in dbx.Stakeholder_UVs
           join addressData in dbx.Stakeholder_Address_UVs
               on data.Stakeholder_ID equals addressData.Stakeholder_ID into tempAddresses
               from addressData in tempAddresses
                .Where(x => x.Effective_Date < DateTime.Today)
                .Where(x => x.Expired_Date > DateTime.Today)
                .Where(x => x.Address_Role  == "Mailing")
                .DefaultIfEmpty() ;

Or filter in table instead of temp data.


var qry = from data in dbx.Stakeholder_UVs
           join addressData in dbx.Stakeholder_Address_UVs
                .Where(x => x.Effective_Date < DateTime.Today)
                .Where(x => x.Expired_Date > DateTime.Today)
                .Where(x => x.Address_Role  == "Mailing")
               on data.Stakeholder_ID equals addressData.Stakeholder_ID into tempAddresses
               from addressData in tempAddresses.DefaultIfEmpty() ;

But, what if more than one addresses appear? Here I added a filter to get the Max addressId back, which should be the recently added:

 
            var qry =  from data in dbx.Stakeholder_UVs
                       join addressData in dbx.Stakeholder_Address_UVs
                            .Where(x => x.Effective_Date < DateTime.Today)
                            .Where(x => x.Expired_Date > DateTime.Today)
                            .Where(x => x.Address_ID == 
                                (dbx.Stakeholder_Address_UVs)
                                .Where(a=>a.Stakeholder_ID == x.Stakeholder_ID)
                                .Max(a=> a.Address_ID))
                           on data.Stakeholder_ID equals addressData.Stakeholder_ID into tempAddresses
                           from ad in tempAddresses 
 

ASP.net Dynamic Data

David Ebbo’s screen cast is very good for show off the new ASP.net Dynamic Data. No wonder this technique alone will become one of the three major MS web solutions. It’s based on Linq to Sql, and very similar to MVC, or MV without C, because it’s still using webForms with many prebuild view templates. The folder structures are same as rails, each module should name after it’s table/model.

The dynamic field is very handy, I like RenderHint, only concern is it’s build on MS Ajax toolkit which is replaced by JQuery now.

Config file for LinQToSql dbml

Rick’s post is the best one I found so far to explain the configuration file for LinQtoSql dbml. My experience is:

  1. Don’t set user’s password in your linqtosql dbml at the very beginning. Figure this configuration magic out first before everything starts.
  2. The “Settings Property Name” key in Connection section itself can give enough information. You need to add a same name connection string in your app config file, but watch out if you are in N-Tier development mode, which means your DAL lib is in different assembly. In that case, you need to use the full typed name “key” (with the DAL name space as the pre-fix) as the connection string name, as Rick emphasised.

LinQ to SQL takes dynamic cretira

I am building a search window to enable user passing dynamic search expression to back end. The old way on those direct connect to database was kind of easy, just need to organize dynamic sql. What about ORM?

In NHibernate we can build criterion then pass to session to do GetByCriteria, I could not find the similar way in Linq to Sql until Dynamic Linq extension appeared on Scott Guthrie’s blog. It just is the one I want!

Scott did mention another choice about predicate builder, I think this is more about merge search result from linq2sql, say you have a lot of ‘Or’ logic in your search query.

I still have a minor problem, how can I let my UI know the column name? Current my BO’s property has no underscore, but the column name in database does. I’ve already used the ‘friendly name’ in Iproperty object by exposing the FieldManager.GetRegisteredProperties().

Do I need to declare another set of column name in my BO somewhere? Or do a mapping in my repository object before the actual linq query? After some time wasting, I decided to re-use this ‘friendly name’, with the under score in the property, those property still look very friendly.

There is a nice tutorial for DynamicQuery, notice how it do the lambda expression in join expression. Here is my result:


        public IEnumerable FindAllByCriteria(string criteria)
        {
            var dbx = DataContextFactory.GetDataContext();

            var dataSet = dbx.Organization_UVs.Where(criteria)
                // This relationship is done by dbml, it's not necessary here. Actually, this will create another join in query.
                //                .Join(dbx.Stakeholder_UVs, a=>a.Stakeholder_ID, b=>b.Stakeholder_ID, (a,b)=>a)
                .Join(dbx.Stakeholder_Name_UVs.Where(n=>n.Legal=="Yes"),
                    data => data.Stakeholder_ID,
                    nameData => nameData.Stakeholder_ID,
                    (data, nameData) => new OrganizationDTO
                            {
                                StakeholderId = data.Stakeholder_ID,
                                IncorporationNumber = data.Incorporation_Number,
                                OrganizationTypeCd = data.Organization_Type_CD,
                                OrganizationStatus = data.Organization_Status,
                                NumberOfMembers = data.Number_of_Members,
                                InitialIncorporationDate = data.Initial_Incorporation_Date,
                                IncorporationStatusDate = data.Incorporation_Status_Date,
                                ParticularsCertifiedDate = data.Particulars_Certified_Date,

                                // base data from stakeholder
                                Expired = data.Stakeholder_UV.Expired,
                                StakeholderType = data.Stakeholder_UV.Stakeholder_Type,
                                LastApprovalDate = data.Stakeholder_UV.Last_Approval_Date,

                                // Name
                                StakeholderNameId = nameData.Stakeholder_Name_ID,
                                Name = nameData.Last_Name,
                            }
                )
             ;

            foreach (OrganizationDTO data in dataSet)
            {
                yield return data;
            }

        }

About Collection

If I want repository return a group of data, which type should I choose? I saw some NHibernate DAO just return IList<T>, JP’s factory pattern returns IEnumerable, Rocky’s CSLA return costomized collection.

Which one is better? After I read this post about collection, I feel the power of the DDD, this post described a typical filter problem:

public class BookCatalog {
List<Book> findBooksByAuthor( … ) {..}
List<Book> findBooksByPublisher( … ) {..}
List<Book> findBooksByAuthorAndPublisher( … ) {..}

Can be easily solve by changing method return type from List<> to costomized collection:

public class BookCatalog {
BookCatalog findByAuthor( … ) {..}
BookCatalog findByPublisher( … ) {..}
BookCatalog findByPublishDates( DateTime,DateTime ) {..}
BookCatalog findByPublishingCountry( … ) {..}

The method chain is here:

BookCatalog.findByAuthor(“Frank”).findByPublisher(“Frank Inc.”)…

The new LinQ where syntax has the similar effect.

I still don’t know why IEnumeralbe should be used, seems it’s usefule when passing data to UI / controls, but why it’s also popular in passing data from DAL to upper layers.

Mock / stub CSLAEx safeDataReader class

Miguel’s CSLAEx provides a very good DAL for CSLA BL. The DataAccess class wraps all the db calls then make data portal function in BL much simpler.

public SafeDataReader ProductGetById(Guid productID)
{
IDataParameter[] parameters =
{
GetParameter("@p_ProductID", productID) as IDataParameter
};

return GetReader("ProductGetById", CommandType.StoredProcedure, parameters);
}

So the BO data portal becomes to this:

protected void DataPortal_Fetch(IdCriteria<Guid, Product> crit)
{
using( SafeDataReader sd =
new DataAccess().ProductGetById(crit.ID))
{
if (sd.Read())
{
_ProductID = sd.GetGuid(sd.GetOrdinal("ProductID"));
_ProductName = sd.GetString(sd.GetOrdinal("ProductName"));
_Description = sd.GetString(sd.GetOrdinal("Description"));
}
}
}

I am very pleased to see he has the same motion to move those sql plumbing code out of BO, but, why do we still see SafeDataReader here. The worse thing is, we have to call SafeDataReader.Read() again to fetch the real data.

I made some modifications then:

public class ProductRepository
{
private DataAccess _DAO = null;

public ProductRepository() : this(new DataAccess()) { }

public ProductRepository(DataAccess dao)
{
_DAO = dao;
}

public ProductDTO GetById(Guid productID)
{

ProductDTO p = new ProductDTO();

SafeDataReader sd = _DAO.ProductGetById(productID);
if (sd.Read())
{

p.ProductID = sd.GetGuid("ProductID");
p.ProductName = sd.GetString("ProductName");
p.Description = sd.GetString("Description");

}

return p;
}

[Serializable()]
public class ProductDTO
{
public Guid ProductID = Guid.Empty;
public string ProductName = String.Empty;
public string Description = String.Empty;
public string DownloadFile = String.Empty;
public decimal UnitPrice = 0;

public ProductDTO(){}

}

public class Product : BusinessBase<Product>
{
private Product() { }

protected override object GetIdValue()
{
return _Data.ProductID;
}

private ProductDTO _Data = new ProductDTO();

[DataObjectField(true, true)]
public Guid ProductID
{
get
{
CanReadProperty("ProductID", true);
return _Data.ProductID;
}
}

public string ProductName
{

get
{
CanReadProperty("ProductName", true);
return _Data.ProductName;
}

set
{
CanWriteProperty("ProductName", true);
if (value != _Data.ProductName)
{
_Data.ProductName = value;
PropertyHasChanged("ProductName");
}
}
}
....
protected void DataPortal_Fetch(IdCriteria<Guid, Product> crit)
{
_Data = new ProductRepository().GetById(crit.ID);
}

protected override void DataPortal_Insert()
{
new ProductRepository().Insert(_Data);
}

protected override void DataPortal_Update()
{
new ProductRepository().Update(_Data);
}

The benefit of this change is, ProductRepository is testable, and injectable. By switching DataAccess class to Linq2Sql DataContext class (using IDataAccess instead), the Repository and BO class won’t need to change too much.

I am disappointed he has less interest in TDD, including DDD. Why do we need this Command Object in CSLA? Can Domain object solve those sql query in a much simpiler way?

Have not learn CSLA’s parent-child yet, but I already got a feeling, CSLA is not good at relationship.

Validation is great, rules can be in 3 different levels, Information, Warning and Error. Also can be stopped by setting a flag. What a thoughtful business framework!

Tried to test those data methods.

[TestFixture]
public class ProductRepositoryTest
{
[Test]
public void GetProductOnBOTestWithoutMock()
{
Product obj = Product.GetProduct(new Guid("309e8408-2d22-45f9-82aa-01a7884c0603"));

Assert.AreEqual(obj.ProductName, "HP Laptop");
}

[Test]
public void GetByIdTestOnRepositoryWithoutMock()
{
ProductRepository r = new ProductRepository(new DataAccess());

ProductDTO p = r.GetById(new Guid("309e8408-2d22-45f9-82aa-01a7884c0603"));

Assert.AreEqual(p.ProductName, "HP Laptop");
}

[Test]
public void GetByIdTestOnRepositoryUsingMock()
{
MockRepository mocks = new MockRepository();
ITrainingDataAccess mockDao = (ITrainingDataAccess)mocks.DynamicMock<ITrainingDataAccess>();

ProductRepository r = new ProductRepository(mockDao);

Guid id = new Guid("309e8408-2d22-45f9-82aa-01a7884c0603");
SafeDataReader sd = (SafeDataReader) mocks.DynamicMock(typeof(SafeDataReader));

using (mocks.Record())
{
Expect
.Call(mockDao.ProductGetById(id))
.Return(sd);
}// Replay and validate interaction
ProductDTO p;
using (mocks.Playback())
{
p = r.GetById(id);

}// Post-interaction assertions
Assert.IsNull(p);

}

The first two are not using mock, passed based on the correct data. The last mock test failed, because the somewhere the proxy object stopped me.

System.MissingMethodException: Constructor on type ‘SafeDataReaderProxyf51d8e1fdc964a8d9baa34c2a4592667’ not found.

I am pretty sure DataReader doesn’t have a construtor, so I tried to changed repository to use ISafeDataReader from SafeDataReader, but there is no ISafeDataReader, google says there is a very similar case in subsonic world, but lucky they are using Interface.

Maybe for CSLAEx we have to use TypeMock to “Mock” SafeDataReader.

Wait a minute, here comes Phil Haack’s StubDataReader, the solution is dirty but easy:


         [Test]
        public void GetByIdTestOnRepositoryUsingMockAndStub()
        {
            Guid id = Guid.NewGuid();

            MockRepository mocks = new MockRepository();
            ITrainingDataAccess mockDao = (ITrainingDataAccess)mocks.DynamicMock<ITrainingDataAccess>();

            ProductRepository r = new ProductRepository(mockDao);

            using (mocks.Record())
            {
                //IDataReader dr = (IDataReader) mocks.CreateMock<IDataReader>();
                StubResultSet resultSet
= new StubResultSet("ProductID", "ProductName", "Description", "UnitPrice", "DownloadFile");

                decimal price = 10m;
                resultSet.AddRow(Guid.NewGuid(), "Stub prod", "some desc", price, "1.img");
                IDataReader dr = new StubDataReader(resultSet);

                SafeDataReader sd = new SafeDataReader(dr);

                Expect.Call(mockDao.ProductGetById(id)).Return(sd);

            }// Replay and validate interaction
            ProductDTO p = null;
            using (mocks.Playback())
            {
                p = r.GetById(id);

            }// Post-interaction assertions
            Assert.IsNotNull(p);
            Assert.AreEqual("Stub prod", p.ProductName);
            Assert.AreEqual("some desc", p.Description);
            Assert.AreEqual("1.img", p.DownloadFile);
            Assert.AreEqual(10m, p.UnitPrice);

        }

Update: (2008-07-16)
I used a internal DTO in my BO here which I think is not right, by doing this BO is tightly coupled to DTO. I then changed to mapper class later. Here is a similar idea about using DTO in CSLA from ASP forum.

TypeMock LinQ DataContext in CSLA BO

Still not quiet clear how this work, but I finally made it, the basic idea is:

I made CSLA BO which calls LinQ DataContext to do the retrieve and update. The main purpose for this is to encapsulate LinQ synatx from CSLA coding.

To test CSLA BO without database, mock LinQ DataContext is necessary. So TypeMock is here, even I add IDataConext into CSLA BO, I found I can not pass this IDataconext as a constructor parameter to it. Fortunately, TypeMock can do kind of ‘internal object injection’ to mock the database!

[Test]
[VerifyMocks]
public void CanGetTraineeName()
{
Gain.DalLinq.Trainee t = new Gain.DalLinq.Trainee();
t.Given_Names = “Frank”;
SerializableEntity<Gain.DalLinq.Trainee> st = new SerializableEntity<DalLinq.Trainee>(t);

// This is the key to mock the future dataconext object.
Mock mocky = MockManager.Mock(typeof(TrainingDataContext));

using (RecordExpectations r = RecorderManager.StartRecording())
{
ITrainingDataContext mockedDbx = new TrainingDataContext();

// Mock GetTrainee method. parameter doesn’t matter.
SerializableEntity<Gain.DalLinq.Trainee> mocked = mockedDbx.GetTrainee(2);

r.Return(st);
}

Trainee item = Trainee.GetTrainee(2);
Assert.AreEqual(“Frank”, item.GivenNames);

[Test]
[VerifyMocks]
[ExpectedException(typeof(DataPortalException))]
public void CanCatchUpdateTraineeException()
{

Gain.DalLinq.Trainee t = new Gain.DalLinq.Trainee();
t.Given_Names = “Frank”;
SerializableEntity<Gain.DalLinq.Trainee> st = new SerializableEntity<DalLinq.Trainee>(t);

Mock mocky = MockManager.Mock(typeof(TrainingDataContext));

using (RecordExpectations r = RecorderManager.StartRecording())
{

ITrainingDataContext dtx = new TrainingDataContext();
SerializableEntity<Gain.DalLinq.Trainee> mocked = dtx.GetTrainee(1);

r.Return(st);

int m = dtx.SaveTrainee(null, null);

r.Return(-1122);
}

Trainee oldTrainee = Trainee.GetTrainee(2);
Assert.AreEqual(“Frank”, oldTrainee.GivenNames);

oldTrainee.GivenNames = “AAAA”;
oldTrainee.Save();

}

[Test]
[VerifyMocks]
public void CanUpdateTrainee()
{

Gain.DalLinq.Trainee t = new Gain.DalLinq.Trainee();
t.Given_Names = “Frank”;
SerializableEntity<Gain.DalLinq.Trainee> st = new SerializableEntity<DalLinq.Trainee>(t);

Mock mockDbx = MockManager.Mock(typeof(TrainingDataContext));

using (RecordExpectations r = RecorderManager.StartRecording())
{

TrainingDataContext dtx = new TrainingDataContext();
SerializableEntity<Gain.DalLinq.Trainee> mocked = dtx.GetTrainee(1);

r.Return(st);

int m = dtx.SaveTrainee(null, null);

r.Return(0);
}

Trainee oldTrainee = Trainee.GetTrainee(2);
Assert.AreEqual(“Frank”, oldTrainee.GivenNames);

oldTrainee.GivenNames = “AAAA”;
Trainee newTrainee = oldTrainee.Save();
Assert.AreEqual(“AAAA”, newTrainee.GivenNames);

}

The idea is from Ben Hall. I add interface in, to focus on testing CSLA only.

Even it’s a great solution to separate dbtest, but I think if we use Windsor Container correctly, those mock tests can be much simpler and clearer, I will post this later.