CODEWITHSUNDEEP
c#sqlsql-serverentity-framework
Marius
Marius

Reputation: 577

Entity Framework SQL

I hope anyone can help me.

How do I build up a query like this in Entity Framework in C#.

My SQL query looks like this:

select Id, dbo.AccessRight(Id, 5)
from Customers
where Id in (select top 10000 Id 
             from Customers 
             where dbo.AccessRight(Id, 5) > 0)

The second select statement should be replaced in code with an list of ids.

Upvotes: 1

Views: 76

Answers (2)

Fabio Milheiro
Fabio Milheiro

Reputation: 8484

Your AccessRight seems to be a user defined function.

If that is the case, then you are stuck with calling context.Database.SqlQuery<T>(YourSQL) as EF doesn't support user defined functions.

UPDATE: You asked What if you have the id list?. Assuming you have an ids list.

from c in Customer
where ids.Contains(c.Id)
select c

However, can't yet run the dbo.AccessRight(Id, 5) user defined function to show in your select result.

You actually can

Forgive my previous lack of knowledge. You can actually call user defined functions with EF.

Inside your Database context, you can do something like this (this code was actually generated by EF EDMX tool although I removed a few things for simplicity):

public virtual ObjectResult<usp_GetAppropriatenessQuestionsWithMetadata_Result> usp_GetAppropriatenessQuestionsWithMetadata(Nullable<int> languageId)
    {
        var languageIdParameter = languageId.HasValue ?
            new ObjectParameter("LanguageId", languageId) :
            new ObjectParameter("LanguageId", typeof(int));

        return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<usp_GetAppropriatenessQuestionsWithMetadata_Result>("usp_GetAppropriatenessQuestionsWithMetadata", languageIdParameter);
    }

Upvotes: 1

uTeisT
uTeisT

Reputation: 2266

IEnumerable<YourTypeHere> myIEnumerable = dbContext.Database.SqlQuery<YourTypeHere>(sqlQueryHere);

OR

List<YourTypeHere> myList = dbContext.Database.SqlQuery<YourTypeHere>(sqlQueryHere).ToList();

Should do the trick

Upvotes: 1

Related Questions