shole shole - 4 months ago 11
C# Question

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:


  • If there is multiple attachments, select ONLY the oldest one(i.e. the
    one with oldest
    CreatedDate
    field value)



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.

Answer

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,