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;
            }

        }