不存在实体的FromSql [英] FromSql with Non-Existing Entity

问题描述

我有一个非常大的现有数据库，其中包含许多视图和表格.我需要结合一些视图和表的结果.我希望在DbContext中可以使用这些合并的数据，但是没有可用于将实体映射到所需SQL结果的单个对应视图或表.

I have a very large, existing database with numerous views and tables. I need to combine the results of some views and tables. I want this combined data to be available in my DbContext but there is no single corresponding view or table that I can use to map the entity to the SQL results I want.

为此，我在上下文中设置了一个DbQuery，并使用OnModelCreating方法设置查询结果，如下所示.

To this end, I have set up a DbQuery in my context and I am using the OnModelCreating method to set up the query results, an example is below.

这是实体对象模型.在此示例中， Author 和 Book 是表，而 AuthorBookCount 是自定义SQL查询的对象模型，该对象模型没有对应的视图或要映射的表

Here are the entity object models. In this example Author and Book are tables and AuthorBookCount is an object model of a custom SQL query that has no corresponding view or table to be mapped to.

public class Author
{
    [Column("ID")]
    public int Id { get; set; }

    [Column("FIRST_NAME")]
    public string FirstName { get; set; }

    [Column("LAST_NAME")]
    public string LastName { get; set; }
}

public class Book
{
    [Column("ID")]
    public int Id { get; set; }

    [Column("AUTHOR_ID")]
    public int AuthorId { get; set; }

    [Column("TITLE")]
    public string Title { get; set; }
}

public class AuthorBookCount
{
    [Column("AUTHOR_FULL_NAME")]
    public string AuthorFullName { get; set; }

    [Column("BOOK_COUNT")]
    public long BookCount { get; set; }
}

然后我像这样设置 DbContext :

public class MyDbContext : DbContext
{
    public DbSet<Author> Authors { get; set; }

    public DbSet<Book> Books { get; set; }

    public DbQuery<AuthorBookCount> AuthorBookCounts { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        //Set up cnxn string
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Query<AuthorBookCount>().ToQuery(() =>
            AuthorBookCounts.FromSql("SELECT DISTINCT(Authors.FIRST_NAME + ' ' + Authors.LAST_NAME) AS AUTHOR_FULL_NAME, COUNT(Books.Id) FROM Authors JOIN Books ON Books.AUTHOR_ID = Authors.ID GROUP BY (Authors.FIRST_NAME + ' ' + Authors.LAST_NAME)");
    }
}

现在，当我尝试使用我的 AuthorBookCounts 属性时，出现SystemNotSupported异常，并显示一条消息，指出当前不支持FromSql.

Now, when I try to make use of my AuthorBookCounts property I get a SystemNotSupported exception with a message stating that FromSql is currently not supported.

我将我的 FromQuery SQL调用更改为LINQ查询，并且可以正常工作.这种解决方案的问题在于，我的SQL调用非常复杂，要继续将我的SQL转换为LINQ语句是一个巨大的痛苦.

I changed my FromQuery SQL call to a LINQ query and it works. The issue with this solution is that my SQL calls are pretty complex and it is a huge pain to keep converting my SQL to a LINQ statement.

对于正在发生的事情或我做错了的任何解释，将不胜感激.

Any explanations as to what is happening or what I am doing wrong would be greatly appreciated.

推荐答案

与所有重大问题一样，解决方案也非常简单.在 ToQuery 方法中，将对 DbQuery< AuthorBookCount> 属性 AuthorBookCounts 的引用替换为另一个 Query< T> 参考，就像这样.

Like all great problems, the solution for this was quite simple. In the ToQuery method, replace the reference to the DbQuery<AuthorBookCount> property AuthorBookCounts with another Query<T> reference, like so.

public class MyDbContext : DbContext
{
    public DbSet<Author> Authors { get; set; }

    public DbSet<Book> Books { get; set; }

    public DbQuery<AuthorBookCount> AuthorBookCounts { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        //Set up cnxn string
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Query<AuthorBookCount>().ToQuery(() =>
            Query<AuthorBookCount>().FromSql(@"SELECT DISTINCT(Authors.FIRST_NAME + ' ' + 
            Authors.LAST_NAME) AS AUTHOR_FULL_NAME, COUNT(Books.Id) FROM Authors 
            JOIN Books ON Books.AUTHOR_ID = Authors.ID GROUP BY (Authors.FIRST_NAME + ' ' + 
            Authors.LAST_NAME)");
    }
}

这解决了我上面遇到的特定错误.我在学习过程中要注意的一些重要事项.如果您经常使用EF，这些内容应该很容易解释.

This resolved the specific error that I was experiencing above. Some important things to note that I learned along the way. These things should be pretty self explanatory if you have been using EF a lot.

1. SQL语句必须为您要将语句映射到的实体对象模型中的每个属性返回一个值.
2. 对象实体属性必须正确映射到SQL语句中返回的字段名称.

我希望这篇文章可以帮助其他人.我发现有帮助的一门课程是Julie Lerman的Entity Framework Core 2.1:PluralSight上的新增功能手册.

I hope this post helps someone else in their endeavors. A course that I found helpful was Julie Lerman's Entity Framework Core 2.1: What's New Playbook available on PluralSight.

这篇关于不存在实体的FromSql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！