Entity Framework SQL

Question

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.

you can do dbContext.Database.Query<T>(sqlQueryString) where T is the dto that represents an Id and AccessRight and sqlQueryString is the sql you have provided — Callum Linington
– Callum Linington, Commented Aug 11, 2016 at 13:05

uTeisT · Accepted Answer · 2016-08-11 13:14:26Z

1

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

OR

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

Should do the trick

answered Aug 11, 2016 at 13:14

uTeisT

2,26616 silver badges27 bronze badges

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

Comments

Fabio Milheiro · Accepted Answer · 2016-08-12 09:09:18Z

1

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

edited Aug 12, 2016 at 9:09

answered Aug 11, 2016 at 13:11

Fabio Milheiro

8,54420 gold badges63 silver badges104 bronze badges

8 Comments

Marius Over a year ago

Yes it is an user defined function.

uTeisT Over a year ago

True but I guess you might need to change context.Database.Query<T>(YourSQL) to context.Database.SqlQuery<T>(YourSQL)

Marius Over a year ago

@uteist But could you please tell me, if I have any chance to pass a List of Ids into? So that I can replace the second "select" in the this call with an List of Ids?

Fabio Milheiro Over a year ago

@Marius, the update will probably be closer to what you want.

uTeisT Over a year ago

Well, I think you could do it in 2 separate queries. First being the user defined function but not in a function. And second, with the data you retrieve from that query, you generate another query which does the actual work.

|

Collectives™ on Stack Overflow

Entity Framework SQL

2 Answers 2

Comments

You actually can

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

You actually can

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related