CODEWITHSUNDEEP
c#sqllinqentity-frameworkentity-framework-6
Admir Tuzović
Admir Tuzović

Reputation: 11177

Linq to Entities join into with DefaultIfEmpty returns no results

Query below yields no results:

var query = from g in context.Groups
            join sl in context.GroupDancerLinks on g.Id equals sl.GroupId into sls
            join usl in context.GroupForeignDancerLinks on g.Id equals usl.GroupId into usls
            from sl in sls.DefaultIfEmpty()
            from usl in usls.DefaultIfEmpty()
            where usl.IsLead
            where sl.IsLead
            select new GroupGridDTO
            {
                Id = g.Id,
                LeadName = sl.Dancer.Name,
                UnregisteredLeadName = usl.ForeignDancer.Name,
                DancersCount = sls.Count(),
                ForeignDancersCount = usls.Count()
            };

However, when same query is modified to use FirstOrDefault, results are found:

var query = from g in context.Groups
            join sl in context.GroupDancerLinks on g.Id equals sl.GroupId into sls
            join usl in context.GroupForeignDancerLinks on g.Id equals usl.GroupId into usls
            select new GroupGridDTO
            {
                Id = g.Id,
                LeadName = sls.Where(sl => sl.IsLead).Select(sl => sl.Dancer.Name).FirstOrDefault(),
                UnregisteredLeadName = usls.Where(usl => usl.IsLead).Select(usl => usl.ForeignDancer.Name).FirstOrDefault(),
                DancersCount = sls.Count(),
                ForeignDancersCount = usls.Count()
            };

Looking on how queries are written, results should be the same. What is wrong with first query?

EDIT:

Tables "Groups" and "ForeignDancers" are related with M2M table "GroupForeignDancerLinks".

Tables "Groups" and "Dancers" are related with M2M table "GroupDancerLinks".

Upvotes: 1

Views: 942

Answers (1)

Servy
Servy

Reputation: 203834

In the first query if a given row has IsLead as being false the row will be removed entirely. In the second query the row exists, you just leave the corresponding item's name blank.

The queries are also semantically different if there are multiple matches for a given row in the left table. The first will list all matching rows, the latter only the first.

Upvotes: 1

Related Questions