Combining Entity Framework SqlQuery with extra restriction in code

Question

I have a SQL query

var sql = "Select * From Foo Where Bar = {0}"

I want to execute this using Entity Framework, but I want to impose an extra restriction, to see if column Id is in a certain range:

List<int> ids = ...;
var MyFoos = context.Foos.SqlQuery<Foo>(sql).Where(x => ids.Contains(x.Id));

Is this likely to result in efficient selection from the database, or would it end up executing the whole of "Select * From Foo Where Bar = {0}" first and only then filtering for the IDs?

My expectation would be the latter, because the query is hardcoded rather than something generated by EF itself, and thus there is no way for compilation to improve the query into a single sql read — John Bingham
– John Bingham, Commented Oct 1, 2013 at 21:40

Khan · Accepted Answer · 2013-10-01 23:01:37Z

2

The SQL statement in sql will be executed database side, and results will be returned to the client.

The filter .Where(x => ids.Contains(x.Id)); will then be executed against the results of your sql query, client side.

The .Where will not be translated to SQL.

I verified this using SQL Profiler on a similar query.

answered Oct 1, 2013 at 23:01

Khan

18.2k5 gold badges52 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Stephen Holt Over a year ago

Thanks, I'll definitely avoid doing that then!

Collectives™ on Stack Overflow

Combining Entity Framework SqlQuery with extra restriction in code

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related