带有自定义模型的实体框架Core Raw SQLQueries [英] Entity framework Core Raw SQLQueries with custom model

问题描述

使用Entity Framework 6，我能够使用执行Raw SQL查询并使用DBContext中未定义的自定义模型来存储查询的输出.一个简单的示例如下:

Using Entity Framework 6, I was able to use execute a Raw SQL Query and use a custom model which was not defined in the DBContext in order to store the output of the query. A simple example is the following:

List<MyModel> data = context.Database.SqlQuery<MyModel>("SELECT Orders.OrderID, Customers.CustomerName FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;").ToList();

我执行一个SQL命令，并且希望有一组自定义模型.

I execute one SQL command and I expect a list of custom models.

我尝试对Entity Framework Core做类似的事情，发现的最接近的示例将迫使我从DBContext定义属性.这将不允许我使用自定义模型填充SQL Server将返回的数据.

I try to do something similar with Entity Framework Core and the closest example that I found will force me to define a property from DBContext. This will not allow me to use a custom model to fill the data that SQL server will return.

var books = context.Books.FromSql("SELECT * FROM Books").ToList();

此查询通知Entity Framework Core该查询将返回书籍列表.有没有办法在Entity Framework Core中实现类似的东西?

This query informs Entity Framework Core that the query will return a list of books. Is there a way to implement something like this in Entity Framework Core?

推荐答案

问题是关于.NET Core 2的.现在我有一个解决方案，我将在这里编写它，以便其他人可以使用它，以防他/她需要它.

The question was about .NET Core 2. Now I have a solution and I am going to write it here so that someone else could use it in case he/she needs it.

首先，我们在dbContext类中添加以下方法

First of all we add the following method in dbContext class

public List<T> ExecSQL<T>(string query)
{
    using (var command = Database.GetDbConnection().CreateCommand())
    {
        command.CommandText = query;
        command.CommandType = CommandType.Text;
        Database.OpenConnection();

        List<T> list = new List<T>();
        using (var result = command.ExecuteReader())
        {
            T obj = default(T);
            while (result.Read())
            {
                obj = Activator.CreateInstance<T>();
                foreach (PropertyInfo prop in obj.GetType().GetProperties())
                {
                    if (!object.Equals(result[prop.Name], DBNull.Value))
                    {
                        prop.SetValue(obj, result[prop.Name], null);
                    }
                }
                list.Add(obj);
            }
        }
        Database.CloseConnection();
        return list;
    }
}

现在我们可以有以下代码.

Now we can have the following code.

List<Customer> Customers = _context.ExecSQL<Customer>("SELECT ......");

这篇关于带有自定义模型的实体框架Core Raw SQLQueries的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！