在Entity Framework中使用存储的查询

I want to use a SQL query in Entity Framework.

我想在Entity Framework中使用SQL查询。

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.

但是tbl是null。你有什么建议吗?我是Entity Framework的新手。

1 个解决方案

#1

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.

那是因为SqlQuery并不意味着返回DataTable。它被构建为将您的实体类型作为IEnumerable 返回,其中T是您的实体类型。使用下面的语句并记住它是一个可返回的可枚举类型。现在用它来执行你需要的东西。

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)

#1

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

Edited

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)

秒客网

在Entity Framework中使用存储的查询

1 个解决方案

#1

Edited

#1

Edited

相关文章