Implementing where clause in LINQ with join

Question

I have this LINQ (LINQ TO ENTITY):

var clientFullReview = (from cr in clientReview
                        join ir in inspectionReviews on cr.siteId equals ir.SiteId into g
                        from subsite in g.DefaultIfEmpty()
                        select new
                        {
                         clientId = cr.clientId,
                         clientName = cr.clientName,
                         siteId = cr.siteId == null ? -1 : cr.siteId,
                         inspectionReviewId = (subsite == null ? -1 : subsite.Id),
                         inspectionFrequency = (subsite == null ? -1 : subsite.FrequencyId),

                          isNormal = (subsite == null ? false : subsite.IsNormal)
                         });

In the link above I need to use where clause.

inspectionReviews - has date property. In the LINQ abouve I want to make join only inspectionReviews records that has specific date ,like that:

    var clientFullReview = (from cr in clientReview
                            join ir in inspectionReviews on cr.siteId equals
                            where ir.DateReview.Year == date.Year &&
                            ir.DateReview.Month == date.Month 
                            into g
                            from subsite in g.DefaultIfEmpty()
                            select new
                            {
                             clientId = cr.clientId,
                             clientName = cr.clientName,
                             siteId = cr.siteId == null ? -1 : cr.siteId,
                             inspectionReviewId = (subsite == null ? -1 : subsite.Id),
                             inspectionFrequency = (subsite == null ? -1 : subsite.FrequencyId),

                              isNormal = (subsite == null ? false : subsite.IsNormal)
                             });

But when I try to implement it with where clause I get this error:

A query body must end with a select clause or a group clause

on this key word: into in second LINQ.

So my question how can I filter data by date when I implement join?

Answer 1

One way

join ir in inspectionReviews.Where(x =>
    x.DateReview.Year == date.Year && x.DateReview.Month == date.Month)
on cr.siteId equals ir.SiteId

Another way

join ir in (from x in inspectionReviews
    where x.DateReview.Year == date.Year && x.DateReview.Month == date.Month
    select x)
on cr.siteId equals ir.SiteId

Yet another way

join ir on cr.siteId equals ir.SiteId into g
from subsite in g
    .Where(x => x.DateReview.Year == date.Year && x.DateReview.Month == date.Month)
    .DefaultIfEmpty()

Answer 2

Join clause allows only equals, so if you need to filter the joined collection, you can use the subsite variable under the second from clause:

join ir in inspectionReviews on cr.siteId equals ir.SiteId
into g
from subsite in g.DefaultIfEmpty()
where subsite.DateReview.Year == date.Year &&
subsite.DateReview.Month == date.Month 

Answer 3

Break it intotwo queries and do your second select from the filterest first list.

I am not going to reproduce your code because I think it has some text missing, on the second join value, but the idea is to do it in two steps:

 var clientFullReviews = (from cr in clientReview
                        join ir in inspectionReviews on cr.siteId equals
                        where ir.DateReview.Year == date.Year &&
                        ir.DateReview.Month == date.Month 
                        into g

 var clientcurrent reviews =(from cr clientFullReviews select new
                        {
                         clientId = cr.clientId,
                         clientName = cr.clientName,
                         siteId = cr.siteId == null ? -1 : cr.siteId,
                         inspectionReviewId = (subsite == null ? -1 : subsite.Id),
                         inspectionFrequency = (subsite == null ? -1 : subsite.FrequencyId),

                          isNormal = (subsite == null ? false : subsite.IsNormal)
                         });

This isn't perfect syntax because I don't know your data objects well enough, but you get the idea. I am not sure if you will take a performance hit doing it this way, but I almost always break it up like this to keep my Linq syntax clean and readable (and to keep from confusing myself with too many extension expressions in one line!)