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:

        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.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)

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.



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 == 
                                .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)
                    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;