CODEWITHSUNDEEP
c#linq-to-entities
Corretto
Corretto

Reputation: 67

LINQ to Entities conditional where clause

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.

Upvotes: 0

Views: 904

Answers (3)

Jon Skeet
Jon Skeet

Reputation: 1503859

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.

Upvotes: 4

Yahia
Yahia

Reputation: 70369

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();
}

Upvotes: 1

Albin Sunnanbo
Albin Sunnanbo

Reputation: 47068

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 
          };

Upvotes: 1

Related Questions