combining linq queries

Question

Hi, i have a couple of linq queries which I wanted to join

var IDs = from p in ctx.bam_Zending_AllInstances
                          where p.Zender == "RT30"
                          select new { Identificatie = p.Identificatie };

                var latestIDs = from p in ctx.bam_Zending_AllInstances
                                where p.Zender == "RT30"
                                group p by p.Identificatie into q
                                select new { Identificatie = q.Key, Datum = q.Max(p => p.PrestatieOntvangen) };

                var final = from p in IDs
                            join q in latestIDs on p.Identificatie equals q.Identificatie
                            select new { Identificatie = p.Identificatie };

The problem is. If I look at the results, the IDs have 308 items, the latestIDs have 304 items, but the final one also has 308 items. How is this possible? I thought a join in linq was an inner join. I need to have 304 items, but instead it does an outer join.

What am I doing wrong?

Answer 1

Are you sure you have unique IDs here? If you have two rows with the same ID, the join will generate four matches instead of two.

I suggest you look at the generated SQL for the final query - I would certainly expect it to be an inner join.

Answer 2

The ID wasn't unique indeed.

I fixed the problem by using two conditions to join:

var IDs = from p in ctx.bam_Zending_AllInstances
                          where p.Zender == "RT30"
                          select new { ID = p.Identificatie, Datum = p.PrestatieOntvangen };

                var latestIDs = from p in ctx.bam_Zending_AllInstances
                                group p by p.Identificatie into q
                                select new { ID = q.Key, Datum = q.Max(p => p.PrestatieOntvangen) };

                var final = from p in IDs
                            join q in latestIDs on new { p.ID, p.Datum } equals new { q.ID, q.Datum }
                            select new { ID = p.ID };