CODEWITHSUNDEEP
c#linq
Tee F
Tee F

Reputation: 285

Left join with where clause in linq

I am trying to do a left join with a where clause in linq.

I have leadsQuery table with 2500 rows. I want to join the LeadCons table into it. For a lead there can be multiple entries in the LeadCons table, hence I want to join only when the Status match. Else I want the fields to be NULL.

var data = from lead in leadsQuery
    join lcs in context.LeadCons on lead.ID equals lcs.LeadId into leadsWithCons
    from lcs in leadsWithCons.DefaultIfEmpty()
    where lead.Status == lcs.Status
    select new
    {
        LeadId = lead.ID,
        Source = lead.Source.ToString(),
    };

This query gives me ~1500 rows and leadsQuery has 2500. What am I doing wrong here?

Upvotes: 0

Views: 339

Answers (2)

FortyTwo
FortyTwo

Reputation: 2639

A late answer, hoping it is still helpful:

First, you aren't selecting any values from LeadCons, so what is the purpose of a join? I shall assume maybe you want to extend your select, so let us say you want to select the property foo, so my next question: Why do you need a left join in your case? You can simply do a select:

var data = from lead in leadsQuery
    select new
    {
        Foo = context.LeadCons.Where(lcs => lead.Status == lcs.Status).SingleOrDefault().foo
        LeadId = lead.ID,
        Source = lead.Source.ToString(),
    };

This way you have the same number of items and for each item the desired foo value.

Upvotes: 1

Raphael Maia Ribeiro
Raphael Maia Ribeiro

Reputation: 91

Have you tried just changing the your join to a join with multiple conditions, and then removing the where 'status equal status'

    from lead in leadsQuery
join lcs in context.LeadCons on new {
     p1 = lead.ID,
     p2 = lead.Status
}
equals
new {
     p1 = lcs.LeadId,
     p2 = lcs.Status
}

you can have a look at this nice article: https://smehrozalam.wordpress.com/2010/04/13/linq-how-to-write-queries-with-complex-join-conditions/

Upvotes: 0

Related Questions