CODEWITHSUNDEEP
.netsqllinq
Andrew Stakhov
Andrew Stakhov

Reputation: 1135

How do you left join using "date between" operator in linq?

I have two tables. The parent table has a single date column, and child table has 2 date columns (From / To). I need to make a left join from parent to child where parent's date column is between one in child. In sql this would look something like this:

select p.cob, count(*) from parent p
left join child c on p.cob between c.effective and c.expiry
group by p.cob

How does one write this in linq - I'm a bit stuck here....

Upvotes: 2

Views: 5793

Answers (4)

S. Kalinowski
S. Kalinowski

Reputation: 113

Old but maybe it will be helpfull for others. It made my query 7 times faster on specific amount of test data!

I had (28seconds)

from st in subtable
join t in table on st.IdTable equals t.Id
Where t.date >= myStartDate
&& t.date <= myEndDate
&& st == some_other_conditions
select st

I changed it to this (4seconds)

from st in subtable
join t in table on new { parent = st.IdTable, from = 1, to = 1 } equals new { parent = t.Id, from = (t.date >= myStartDate ? 1 :  0), to = (t.date <= myEndDate ? 1 : 0 )
Where st == some_other_conditions
select st

It checks other conditions after creating result of join so all the rest works on smaller amount of data.

Hope it helps

Upvotes: 2

onedaywhen
onedaywhen

Reputation: 57093

I need to make a left join from parent to child

There is always more than one way to express a requirement in SQL and I'm sure the same is true of linq e.g. 

SELECT p.cob, COUNT(*) 
  FROM parent p
 WHERE EXISTS (
               SELECT * 
                 FROM child c 
                WHERE p.cob BETWEEN c.effective AND c.expiry
              )
 GROUP 
    BY p.cob
UNION
SELECT p.cob, 0 
  FROM parent p
 WHERE NOT EXISTS (
                   SELECT * 
                     FROM child c 
                    WHERE p.cob BETWEEN c.effective AND c.expiry
                  );

A good optimizer will spot the repeated derived table, that the two union'd tables are mutually exclusive, etc.

Upvotes: -1

Aducci
Aducci

Reputation: 26694

This should be what you are looking for

var query = from p in context.Parent
            from c in context.Child.Where(x => p.cob >= x.effective)
                                   .Where(x => p.cob <= x.expiry)
                                   .DefaultIfEmpty()
            group p by p.cob into pg
            select new
            {
              cob = pg.Key,
              count = pg.Count()
            };

Upvotes: 4

Andy Evans
Andy Evans

Reputation: 7176

If I read your question correctly, the between part would be handled like this.

where p.cob >= c.effective and p.cob <= c.expiry

Upvotes: 0

Related Questions