CODEWITHSUNDEEP
c#entity-frameworklinqdatetimelambda
frododot
frododot

Reputation: 127

LINQ in Entity Framework append onto DATETIME WHERE clause

I'm trying to build a WHERE clause during run time. I have created a query with the initial WHERE clause:

var origSQL = (from ....
               join ...
               where ...
               select new { ... } );

and will be adding the following WHERE to origSQL: 

var endDate = (from table1 in db.Table1
               join aliasTable1 in db.Table1 on table1.id equals aliasTable1.id
               where aliasTable1.anotherId  == anInputStr
               && (table1.field1 == aliasTable1.field1)
               select DbFunctions.TruncateTime(aliasTable1.endDate)).Max();
...

The endDate will need further refinement prior to attaching to the original WHERE, like:

if (!string.IsNullOrEmpty(anotherInputStr) && (anotherInputStr == "FOO"))
   endDate = endDate.Where ( a=> aliasTable1.GRADE == "F" );

On this, I get error: 

DateTime does not contain a definition for Where accepting a first argument of type DateTime

So I removed the select portion from the initial endDate, figuring I can add it later:

var endDateCriteria = from table1 in db.Table1
               join aliasTable1 in db.Table1 on table1.id equals aliasTable1.id
               where aliasTable1.anotherId  == anInputStr
               && (table1.field1 == aliasTable1.field1)

if (!string.IsNullOrEmpty(anotherInputStr) && (anotherInputStr == "FOO"))
   endDateCriteria = endDateCriteria .Where ( a=> aliasTable1.GRADE == "F" );

But the 

.Where

is causing issue still. I need to append a bunch of these checks to be added onto this WHERE, to then be appended onto the original query, and can't locate the correct syntax.

Added This is originally a self-join. The original dynamic SQL looks likes this

origSQL := ' AND table1.endDate = (
                 SELECT MAX(endDate)
                 FROM table1 aliasTable1   
                 WHERE aliasTable1.ID = table1.ID)

And I am trying to append additional WHERE clauses onto this one, which then in its entirety will be appended onto the main query. I don't know the syntax for this.

Upvotes: 0

Views: 85

Answers (2)

Chad McGrath
Chad McGrath

Reputation: 1601

Edited answer with conditional mentioned in the comment by the original poster:

var endDates = (from table1 in db.Table1
               join table2 in db.Table2 on table1.id equals table2.id
               where table2.anotherId  == anInputStr
               && (table1.field1 == table2.field1);
if(YOUR CONDITION)
{
 endDates = endates.Where(e=> e.).Grade == "F")
}
var endate = from endates select DbFunctions.TruncateTime(table2.endDate)).Max();

You were originally operating on a single object (the Maximume table2.enddate) .Where acts on a collection.

var endDate = (from table1 in db.Table1
               join table2 in db.Table2 on table1.id equals table2.id
               where table2.anotherId  == anInputStr
               && (table1.field1 == table2.field1)

&& table(I don't know which table).Grade == "F"

               select DbFunctions.TruncateTime(table2.endDate)).Max();

Upvotes: 0

Andrew
Andrew

Reputation: 7880

Every query must have a select, which will define what your variable will hold. In your second example you have none, so that doesn't compile. You should do something like this:

var endDateCriteria = from table1 in db.Table1
                        join aliasTable1 in db.Table1 on table1.id equals aliasTable1.id
                        where aliasTable1.anotherId == anInputStr
                        && (table1.field1 == aliasTable1.field1)
                        select new { table1, aliasTable1 };

if (!string.IsNullOrEmpty(anotherInputStr) && (anotherInputStr == "FOO"))
    endDateCriteria = endDateCriteria.Where(q => q.aliasTable1.GRADE == "F");

if (somethingElse)
    endDateCriteria = endDateCriteria.Where(q => q.aliasTable1.field1 > value);

if (otherCondition)
    endDateCriteria = endDateCriteria.Where(q => q.table1.field2 != value2);

DateTime result = endDateCriteria.Max(q => q.aliasTable1.endDate).Date;

Here I'm storing in endDateCriteria both tables in an anonymous object, so I can later continue using both tables for my conditions, and in the end I get the maximum date. I don't see the need to use TruncateTime.

Upvotes: 1

Related Questions