CODEWITHSUNDEEP
c#entity-frameworkentity-framework-6
MrZander
MrZander

Reputation: 3120

Entity Framework append where clause to SqlQuery call

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)?

Upvotes: 2

Views: 1716

Answers (1)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726849

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);
}

Upvotes: 4

Related Questions