Entity Framework append where clause to SqlQuery call

c# entity-framework entity-framework-6

Question

The following raw query is being run by Entity Framework using aSqlQuery contact theDbSet :

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, however because of how slowlySqlQuery automatically executes without theWhere becoming used.

Is there a way to contact you?SqlQuery in a manner that theWhere is implemented server-side?

In essence, I want Entity Framework to produce a store query that looks like:

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

The where clause isn't usually that straightforward, therefore is there a way to convert my where phrase into a query that I can manually append?

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

Accepted Answer

Since "raw" queries are of type DbRawSqlQuery<TElement>, which lacks the necessary functionality for "composing" dynamic queries using LINQ methods, this is not possible.Where .

You might get around the problem by adopting the following condition if you are interested in counting things:

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