Where clause in LINQ - C#

Question

I have the following which works in SQL Query Analyzer.

select oh.*
from order_history oh
join orders o on o.order_id = oh.order_id
where oh.order_id = 20119 and oh.date_inserted = (
    select max(date_inserted) from order_history where order_id = oh.order_id
    group by order_id
)

How would I go about converting to LINQ? From test code, the compiler complained:

Error Operator '&&' cannot be applied to operands of type 'int' and 'System.DateTime'

My LINQ code:

var query = from ch in cdo.order_histories
    join c in cdo.orders on ch.order_id equals c.order_id
    where (ch.order_id.equals(1234)) &&
    (ch.date_inserted == (cdo.order_histories.Max(p => p.date_inserted)))
    select new OrderData() { };

Update: I was not using '==' for comparing.

Item remaining is this from my SQL query:

oh.date_inserted = (
select max(date_inserted) from order_history where order_id = oh.order_id
group by order_id)

How do I do this in LINQ?

Answer 1

Agreed, some C# code is needed here, but off the top of my head- you're using "==" (evaluation) rather than "=" (assignment), correct? C# makes a distinction here where SQL does not.

Answer 2

It look like you are missing an equals sign somewhere when filtering on the order_id field. You probably have:

oh.order_id = 20119 && ...

Whereas you should have:

oh.order_id == 20119 && ...

Note the equality operator vs. the assignment operator. The result of an assignment operator is the value that was assigned, which is why your error says you can't compare operands of int and System.DateTime.

I also assume you have the same problem on the check against the value of date_inserted as well.

For the second part of your question, you are close in the conversion of the correlated sub query.

In SQL you have:

oh.date_inserted = (
select max(date_inserted) from order_history where order_id = oh.order_id 
group by order_id)

And in LINQ-to-SQL you have

ch.date_inserted == (cdo.order_histories.Max(p => p.date_inserted))

You just have to add the filter for the order_histories which takes advantage of closures to capture the order_id value on the ch instance like so:

ch.date_inserted == (cdo.order_histories.
    Where(ch2 => ch2.order_id == ch.order_id).
    Max(p => p.date_inserted))

Answer 3

You could translate the SQL into LINQ... or you could write the LINQ for what you want.

var result = cdo.order_histories
  .Where(oh => oh.order_id == 20119)
  .OrderByDescending(oh => oh.date_inserted)
  .Take(1)
  .Select(oh => new {history = oh, order = oh.order}
  .Single();