user2496448 user2496448 - 7 months ago 22
SQL Question

query of join in entities using lambda expression

I am using VS12 and entity framwork in my web application.
Made baseRepository for get data from database(sql server 2008 R2) in which query context are GetAll(), where, findBy,add,update,saveChanges,dispose,getById().
Use this repository for transaction.
In One case use join for get data filtered from three different entities.

can any one give example for the same..

Answer

I resolved this problem this way,

  using (var db = new KitRepository<ServiceLevel>())
            {               
                using (var kit = new KitRepository<Kit>())
                {
                    var kitServiceLevel = kit.Search(o => o.PatientId == patientId).Select(i => i.kitServiceLevelId).ToList();
                    var temp = db.Where(o => o.HospitalMasterId == HospitalId && kitServiceLevel.Contains(o.ServiceLevelId) && o.IsEnabled == true).Select(i => i.TabletRequired).ToList();
                    var entity = db.Search(l => temp.Contains(l.TabletRequired) && lstServiceType.Contains(l.ServiceCodeTypeEnumId)).ToList();

                    var model = new List<ServiceLevelModel>();
                    Mapper.Map(entity, model);
                    return model;
                }
            }

If any one optimized the query

 var kitServiceLevel = kit.Search(o => o.PatientId == patientId).Select(i => i.kitServiceLevelId).ToList();
                        var temp = db.Where(o => o.HospitalMasterId == HospitalId && kitServiceLevel.Contains(o.ServiceLevelId) && o.IsEnabled == true).Select(i => i.TabletRequired).ToList();
                        var entity = db.Search(l => temp.Contains(l.TabletRequired) && lstServiceType.Contains(l.ServiceCodeTypeEnumId)).ToList();

Then let me know.

Comments