在LINQ数据库查询中使用接口 [英] Using interfaces in LINQ database queries

问题描述

我正在开发一个应用程序的一部分，该应用程序只是从数据库中提取信息并将其显示给用户.为简单起见，让我们假设我有一个包含两个表Cats和Dogs的数据库.这两个表都有手动分配的主键，并且从不重复/重叠.我试图实现的目标是执行1个LINQ查询，该查询将同时concat两个表.

I am working on part of an application that simply pulls information from the database and displays it to users. For simplicity sake, let us assume I have a database with two tables, Cats and Dogs. Both tables have manually assigned primary keys and are never duplicated/overlapped. The goal I am trying to achieve is to perform 1 LINQ query that will concat both tables.

我最近问了这个问题，该问题涉及对两个集合的LINQ concat执行LINQ concat在代码中手动创建的对象Cats和Dogs.我建议您阅读上一个问题，因为它将使您对该问题有更多的了解.

I recently asked this question regarding performing a LINQ concat on two collections of objects, Cats and Dogs, that were manually created in code. I advise reading the previous question as it will give much insight to this one.

我希望使用接口的原因是为了简化查询.我目前有一个解决方案，将我需要的每个列.Select转换为匿名类型.在这种情况下，此方法可以很好地工作，但会占用我正在使用的数据的页面.

The reason I wish to use interfaces is to simplify my queries. I currently have a solution that .Select each of the columns I need into an anonymous type. This would work fine for this instance, but will consume pages with the data I am working with.

上一个问题与这个问题的不同之处在于，我试图从数据库中提取这些动物.根据我的分析，似乎.NET或Entity Framework无法将我的database与我的interface

The different between that previous question and this one is that I am trying to pull these animals from a database. From my analysis, it seems that .NET or Entity Framework is not able to relate my database to my interface

模型(来自旧问题)

public interface iAnimal
{
    string name { get; set; }
    int age { get; set; }
}
public class Dog :iAnimal
{
    public string name { get; set; }
    public int age { get; set; }
}
public class Cat:iAnimal
{
    public string name { get; set; }
    public int age { get; set; }
}

以下是我尝试过的一些不同的LINQ查询以及由此引起的错误.第一个示例将使用上一个问题的解决方案.

Here are some different LINQ queries I have tried and the resulting error. The first example will be using the solution from the previous question.

var model = _db.Cats.Concat<iAnimal>(_db.Dogs).Take(4);

System.ArgumentException: DbUnionAllExpression requires arguments with compatible collection ResultTypes.

无协方差:

var model = _db.Cats.Cast<iAnimal>().Concat(_db.Dogs.Cast<iAnimal>());

System.NotSupportedException: Unable to cast the type 'Test.Models.Cat' to type 'Test.Interfaces.iAnimals'. LINQ to Entities only supports casting Entity Data Model primitive types.

由于上述错误，由于它没有映射到任何特定的表，因此我似乎无法使用接口与数据库进行交互.

From the above error, it looks like I am not able to use interfaces to interact with databases as it is not mapped to any particular table.

任何见识将不胜感激.谢谢

Any insight would be much appreciated. Thanks

编辑 响应@Reed Copsey，使用您的解决方案，我得到与示例without covariance相同的错误.我尝试更改视图的类型以匹配错误所建议的内容，从而导致此错误

EDIT In response to @Reed Copsey, with your solution, I get the same error as my example without covariance. I tried changing the view's type to match what the error recommends, which results in this error

System.InvalidOperationException: The model item passed into the dictionary is of type 'System.Data.Entity.Infrastructure.DbQuery`1[Test.Interfaces.iAnimal]', but this dictionary requires a model item of type 'System.Collections.Generic.IEnumerable`1[Test.Models.Cat]'.

推荐答案

您的数据库对您的界面一无所知，您可能将无法使它正常工作.我看到两个选择.

You database knows nothing about your interface and you will probably not be able to get this working. I see two options.

您可以使用继承(例如，实体框架支持的继承)，并从一个公共基础实体继承这两个实体.比起您可以对基本类型执行查询，但这可能需要更改数据模型，具体取决于您在数据库级别实现继承的方式.

You could use inheritance - for example supported by the Entity Framework - and inherit both entities from a common base entity. Than you will be able to perform queries against the base type but this may require changes to your data model depending on the way you implement inheritance at the database level.

查看 TPT继承和 TPH继承.还有其他继承模型，例如TPC继承，但目前缺乏设计人员支持.

Have a look at the documentation for TPT inheritance and TPH inheritance. There are still other inheritance models like TPC inheritance but they currently lack designer support.

第二个选项是将两个表中的结果都提取到内存中，并使用LINQ to Objects将它们合并到单个集合中.

The second option is to fetch results from both tables into memory and use LINQ to Objects to merge them into a single collection.

var dogs = database.Dogs.Take(4).ToList();
var cats = database.Cats.Take(4).ToList();

var pets = dogs.Cast<IPet>().Concat(cats).ToList();

还请注意您的查询

var model = _db.Cats.Concat<iAnimal>(_db.Dogs).Take(4);

似乎设计得不是很好-结果肯定取决于所使用的数据库，但是如果您通常只得到前四只猫而从未见过任何狗，我不会感到惊讶.

seems not really well designed - the result will definitely depend on the database used but I would not be surprised if you usually just get the first four cats and never see any dog.

这篇关于在LINQ数据库查询中使用接口的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！