Using interfaces in LINQ database queries

Question

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

When you concat them together, how is it able to tell which iAnimal references which datasource? Can you do this in SQL itself and be able to differentiate the two sources? In other words, if you did SELECT ID, Name, Age FROM Dogs UNION SELECT ID, Name, Age FROM Cats and you only looked at the result set, would you be able to differentiate the source for each record? — SPFiredrake
– SPFiredrake, Commented Jan 8, 2013 at 19:44
In NHibernate, you can use inheritance only if you define a discriminator. Maybe Entity have a similar requirement. — Ortiga
– Ortiga, Commented Jan 8, 2013 at 19:51
There is no requirement for such behavior on this page. It is strictly read only. — Jeff
– Jeff, Commented Jan 8, 2013 at 20:10

Daniel Brückner · Accepted Answer · 2013-01-08 19:54:33Z

1

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.

edited Jan 8, 2013 at 19:54

answered Jan 8, 2013 at 19:47

Daniel Brückner

59.8k16 gold badges103 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

16 Comments

Jeff Over a year ago

I believe I tried this, but it didn't work. I'll try again... Stay tuned. The query I wrote was more of a proof of concept. I didn't want 1000+ items being returned. I just wanted to test for functionality of .Concat first.

Jeff Over a year ago

This solution works, but what if the dataset is large? If I recall, ToList() is a greedy operator. This solution would run the two queries. Would it be possible to manipulate this to only touch the database once?

Forty-Two Over a year ago

This may be neither here nor there, but I'm not totally seeing how this (not just this answer, but the use of an interface in this scenario in general) simplifies or otherwise eases what would otherwise be var query = db.Cats.Concat(db.Dogs).

Daniel Brückner Over a year ago

I am quite sure it is not possible to merge both queries into one unless you use inheritance. Do you really need a single query? I can not think of many cases where this would make any difference. If you want 100 pets, just try to get 100 dogs and if there are less than that fetch 100 - number-of-dogs-returned cats. If you want a list of the 100 youngest pets than things become harder - you do not know how many cats and dogs to fetch. But you could still fetch the 100 youngest cats and 100 youngest dogs and merge them in your application - overhead of factor two, not to bad.

Daniel Brückner Over a year ago

var query = db.Cats.Concat(db.Dogs) will just not work unless you modeled both entities using inheritance. If you do this, you can just query db.Pets, db.Pets.OfType<Cat>() and db.Pets.OfType<Dog>().

|

Collectives™ on Stack Overflow

Using interfaces in LINQ database queries

1 Answer 1

16 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

16 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related