Entity Framework 6 related entity where datetime clause doesn't translate to SQL

c# entity-framework-6

Accepted Answer

The second portion of the query, in my opinion, is not at all being sent to the SQL.

This instructs SQL to return all ChannelValues whose Channel ID equals the specified value.

context.Channels
  .First(ch => ch.Channel_ID == id)
  .ChannelValues

Next, memory executes the second where clause.

.Where(cv => start < cv.ValueTime && cv.ValueTime <= stop);

You must do the following actions in the database to put everything into action:

context.Channels
  .Where(ch => ch.Channel_ID == id)
  .SelectMany(x => x.ChannelValues)
  .Where(cv => start < cv.ValueTime && cv.ValueTime <= stop)

I'm not sure whether this addresses your question about how to accomplish this without having access to the database context, however.

1
8/23/2018 2:48:55 PM

Popular Answer

"EF creates some highly costly queries" well, you have power over how you would want to utilise the framework with its limitations*, it's not the fault of the framework if you know something is wrong but still do it that way.

You might carry it out just as you have.

I.e., how would you go about doing this in raw SQL.

Do the same in EF after that.

var channel = context.Channels.First(x=> x.Channel_ID == id)
channel.ChannelValues = context.ChannelValues.Where(x => x.Channel_ID == id 
                                             && start < x.ValueTime 
                                             && x.ValueTime <= stop
                              ).ToList();


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow