CODEWITHSUNDEEP
c#entity-frameworklinqlinq-to-entities
shole
shole

Reputation: 4084

How to perform a left join with an additional filtering in LINQ to entities?

I have several tables, the main one is called DefectRecord, others are called DefectArea, DefectLevel...etc and the one called DefectAttachment. And this problem is about joining DefectRecord with other tables to get a ViewModel for further use. What the hard part I am facing is about the DefectAttachment table.

DefectRecord has a 1-to-many relation with DefectAttachment. While there may be NO attachment at all for one defect record, there may be multiple attachments.

Logically I tried to perform a left join among DefectRecord & DefectAttachment, but there is one more requiredment:

I am stuck at this requirement, how can I perform this with LINQ-to-Entities? Below is the code of what I have now:

 var ret = (from dr in defectRecordQuery
            join ft in filterQuery on dr.FilterID equals ft.FilterID
            join l in levelQuery on dr.LevelID equals l.LevelID
            join a in attachmentQuery on dr.DefectRecordID equals a.DefectRecordID into drd  
            from g in drd.DefaultIfEmpty()
            select new DefectRecordViewModel
            {
                DefectRecordCode = dr.Code,
                DefectAttachmentContent = g == null ? null : g.FileContent,
                LookupFilterName = ft.FilterName,
            }).ToList();

The *Query variable are the IQueryable object which get the full list of corresponding table.

Upvotes: 1

Views: 233

Answers (1)

Gilad Green
Gilad Green

Reputation: 37299

Group your results by the Code and FilterName and then for the content take that of the item in the group that has the oldest date

var ret = (from dr in defectRecordQuery
            join ft in filterQuery on dr.FilterID equals ft.FilterID
            join l in levelQuery on dr.LevelID equals l.LevelID

            join d in attachmentQuery on dr.DefectRecordID equals d.DefectRecordID into drd
            from g in drd.DefaultIfEmpty()
            group g by new { dr.Code, ft.FilterName } into gg

            select new DefectRecordViewModel
            {
                DefectRecordCode = gg.Key.Code,
                DefectAttachmentContent = gg.OrderByDescending(x => x.CreateDateTime).FirstOrDefault() == null? null: gg.OrderByDescending(x => x.CreateDateTime).FirstOrDefault().FileContent,
                LookupFilterName = gg.Key.FilterName,
            }).ToList();

If using C# 6.0 or higher then you can do:

DefectAttachmentContent = gg.OrderByDescending(x => x.CreateDateTime)
                            .FirstOrDefault()?.FileContent,

Upvotes: 1

Related Questions