Using stored query in Entity Framework

Question

I want to use a SQL query in Entity Framework.

Here is my code:

 string sql = (@"SELECT G.GainId,
                (SELECT Name FROM Carrier WHERE CarrierId = G.CarrierId) AS Carrier,
                (SELECT Name + ' ' + Surname FROM [User] WHERE UserId = G.GainerId) AS Gainer,
                (SELECT Name + ' ' + Surname FROM [User] WHERE UserId = G.GiverId) AS Giver,
                (SELECT Name + ' ' + Surname FROM [User] WHERE UserId = G.CustomerId) AS Customer,
                    P.Name,
                    G.Gained,
                    G.Paid
                    FROM Gain AS G 
                    INNER JOIN Product AS P ON P.ProductId = G.ProductId");

            DataTable tbl = _context.Database.SqlQuery<DataTable>(sql) as DataTable;

But tbl is null. Do you have any suggestions? I am new with Entity Framework.

Mike Perrenoud · Accepted Answer · 2012-08-06 11:57:12Z

5

That's because SqlQuery is not meant to return a DataTable. It's built to return your entity types as an IEnumerable<T> where T is your entity type. Use the below statement and remember that it's an enumerable type that's being returned. Now perform with it what you need.

var tbl = _context.Database.SqlQuery<{your entity type}>(sql)

Edited

Yes, in fact you can very easily build this type. In fact, because the query seems so isolated you could probably build a private class inside of the class it's executed in so that how I'll build the example. However, that's something you need to decide. Below I'll give you a template for the class. The two properties with ?? I don't know exactly what type they correlate to so you'll need to plug those in.

private class QueryResult
{
    public int GainId { get; set; }
    public string Carrier { get; set; }
    public string Gainer { get; set; }
    public string Giver { get; set; }
    public string Customer { get; set; }
    public string Name { get; set; }
    public ?? Gained { get; set; }
    public ?? Paid { get; set; }
}

And then issue the statement like this...

var tbl = _context.Database.SqlQuery<QueryResult>(sql)

edited Aug 6, 2012 at 11:57

answered Aug 6, 2012 at 11:41

Mike Perrenoud

68.1k32 gold badges167 silver badges238 bronze badges

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

2 Comments

cagin Over a year ago

Thank you for your answer Mike. I select values from a few tables in my sql query. So I don't have a entity type which has the properties of result data. That is why I want to use DataTable. Do you have any other suggestion?

cagin Over a year ago

I was thinking about it. Now I am sure this is the one way to solve my problem. Thank you for your help.

Collectives™ on Stack Overflow

Using stored query in Entity Framework

1 Answer 1

Edited

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Edited

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related