Entity Framework append where clause to SqlQuery call

c# entity-framework entity-framework-6

Question

I have the following raw query being executed through Entity Framework via a SqlQuery call on the DbSet:

public IEnumerable<TEntity> GetComplicatedData<TEntity>()
{
    return database
        .Set<TEntity>()
        .SqlQuery("SELECT * FROM <Complicated Query Here>");
}

...

var count = GetComplicatedData<Item>()
    .Where(f => f.OwnerID == 5)
    .Count();

This works, but is very slow due to the fact that SqlQuery executes immediately without the Where getting applied.

Is there any way to call SqlQuery in such a way that the Where gets applied server-side?

Essentially, I want Entity Framework to generate a store query like:

SELECT 
    <Columns> 
FROM 
(
    SELECT * FROM <Complicated Query Here>
) a
WHERE a.OwnerID = 5

Or, is there a way to translate my where expression into a query that I can append manually (a.k.a, without manually writing a SQL query, the where clause is not always that simple)?

1
1
9/12/2018 7:28:46 PM

Accepted Answer

This cannot be done with LINQ methods, because "raw" queries are of type DbRawSqlQuery<TElement>, which lacks support required for "composing" dynamic queries with Where.

If you are interested in counting items, you could work around the issue by taking a condition:

public int CountComplicatedData<TEntity>(Func<TEntity,bool> condition) {
    return database
        .Set<TEntity>()
        .SqlQuery("SELECT * FROM <Complicated Query Here>")
        .Count(condition);
}
2
9/12/2018 7:17:34 PM


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