CODEWITHSUNDEEP
c#asp.net-mvclinq
Jeff
Jeff

Reputation: 2313

Using interfaces in LINQ database queries

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.

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.

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.

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

Model (From old question)

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; }
}

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.

Without Covariance:

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

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]'.

Upvotes: 1

Views: 2268

Answers (1)

Daniel Br&#252;ckner
Daniel Br&#252;ckner

Reputation: 59685

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.

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.

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();

Also note that your query

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.

Upvotes: 1

Related Questions