LINQ to Entities conditional where clause

Question

I have the following query which works okay. However it doesn't work in a join query, where it's needed.

var ra = from c in _context.Wxlogs
         select c;

if (year == "2011")
{
    ra = (IQueryable<Wxlog>)(from c in _context.Wxlogs
                             where c.LogYear == year 
                                   && (SqlFunctions.DatePart("Month", c.LogDate2) == m3) 
                                   && c.LogTime.Contains("23:59")
                             orderby c.LogDate2
                             let LogDate = c.LogDate2
                             select new { 
                                 LogDate, 
                                 c.Rain_today 
                             });
}
else if (year != "2011")
{
    ra = (IQueryable<Wxlog>)(from c in _context.Wxlogs
                             where c.LogYear == year 
                             && c.LogMonth == mm 
                             && c.LogTime.Contains("08:59")
                             orderby c.LogDate2
                             let LogDate = EntityFunctions.AddDays(c.LogDate2, -1)
                             select new { 
                                 LogDate, 
                                 c.Rain_today 
                             });
}

Hence I've been trying to embed the else if conditions ( something like this answer by Whaheed ) without any luck.

Any help would be appreciated.

Doesn't work how? Doesn't give you the data you're expecting, throws an error, or? — Tim, Aug 21 '11 at 05:49
The join gives this error: "The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'Join'." — Corretto, Aug 21 '11 at 05:59

score 4 · Accepted Answer · answered Aug 21 '11 at 06:25

You can use var with a conditional operator:

var query = year == "2011" ?
                 from c in _context.Wxlogs
                 where c.LogYear == year 
                 && (SqlFunctions.DatePart("Month", c.LogDate2) == m3) 
                 && c.LogTime.Contains("23:59")
                 orderby c.LogDate2
                 let LogDate = c.LogDate2
                 select new { 
                     LogDate, 
                     c.Rain_today 
                 });
// Second part of conditional
               : from c in _context.Wxlogs
                 where c.LogYear == year 
                 && c.LogMonth == mm 
                 && c.LogTime.Contains("08:59")
                 orderby c.LogDate2
                 let LogDate = EntityFunctions.AddDays(c.LogDate2, -1)
                 select new { 
                     LogDate, 
                     c.Rain_today 
                 });

It's not ideal, but as you're also changing the "LogDate" bit based on the year, it's probably the simplest approach there is - the two queries differ in too many places to be extracted in the normal way. (It's not like you've actually just got a conditional "where" clause as your title suggests.)

The reason you need var here is because you're projecting to an anonymous type. If you weren't doing that, you could use the if/else block. There are still ways you can do that, but it's slightly painful.

Thanks very much for this. Got the first part to work with a bit of formatting. Much appreciated. Always battle to get an appropriate title too :-) — Corretto, Aug 21 '11 at 06:28

score 1 · Answer 2 · answered Aug 21 '11 at 06:01

You can not cast new { LogDate, c.Rain_today } to Wxlog you need to return select c from both queries.

If you want to select only that part you can after the last else type the following

var ra2 = from r in ra
          select new {
              LogDate, 
              c.Rain_today 
          };

Yahia · Answer 3 · 2011-08-21T06:21:08.307

try (EDIT after comment):

if (year == "2011")
{
ra = (from c in _context.Wxlogs
      where c.LogYear == year && 
            && (SqlFunctions.DatePart("Month", c.LogDate2) == m3) 
            && c.LogTime.Contains("23:59") 
            orderby c.LogDate2
            let LogDate = EntityFunctions.AddDays(c.LogDate2, year == "2011" ? 0 : -1)
            select new { 
                        LogDate, 
                        c.Rain_today 
                        }).AsQueryable();
}
else if (year != "2011")
{
ra = (from c in _context.Wxlogs
      where c.LogYear == year && 
            && c.LogMonth == mm 
            && c.LogTime.Contains("08:59")
            orderby c.LogDate2
            let LogDate = EntityFunctions.AddDays(c.LogDate2, year == "2011" ? 0 : -1)
            select new { 
                        LogDate, 
                        c.Rain_today 
                        }).AsQueryable();
}

Thanks for that, but am getting an Ambiguous innvocation error on the Where clause — Corretto, Aug 21 '11 at 06:16

LINQ to Entities conditional where clause

3 Answers3