Dynamicaly changing the type of a parameter in an Expression without knowing the target type at code time

Question

Lets say i have an Animal class which has some descendants that derive from it like Dog Cat and Mouse

public class Animal{}
public class Dog : Animal {}
public class Cat : Animal {}
public class Mouse: Animal {}

Now lets say entities of those objects are stored in sql database and that i use Entity Framework for communication with that database. Dogs, cats and mouses are all stored in different tables, but in code they share the same parent from which they derive.

If i want to get all animals that fulfill the same expression i would have to query each DbSet separately and give it the same expression but with different type of parameter, so a cat gets a cat parameter type and a dog gets a dog parameter like this

var cats = context.Cats.Where(p=>some expression);
var dogs= context.Dogs.Where(p=>some expression);
var mice= context.Mice.Where(p=>some expression);
var animals = new List<Animal>();
animals.AddRange(cats);
animals.AddRange(dogs);
animals.AddRange(mice);

But, this poses a problem to me, because if i ever wanted to add another animal type for example Bird i would have to add another line of code that gets the data from database and adds it to the result collection. This behaviour is badly managable and i would want for it to loop through types that derive from Animal and programaticaly construct an expression for proper type based on provided source expression which is passed as a parameter to method as a Animal type expression. Something like this:

public List<Animal> GetAnimals(Expression<Func<Animal, bool>> expression)
{
    var animalTypes = GetTypesDerivingFrom(typeof(Animal));
    List<Animal> animals = new List<Animal>();
    foreach(var animalType in animalTypes)
    { 
        var typeTranslatedExpression = GetTypeTranslatedExpression(expression); //i dont know how to do this part
        var portionOfAnimals = context.Set(animalType).Where(typeTranslatedExpression).ToList();
        animals.AddRange(portionOfAnimals);
    }
    return animals;
}

Is there any way to do this? I thought about changing the expression parameter type but i cant seem to figure it out without knowing the proper parameter type at code time.

You can use GetType() and you can use also GetType().ToString(). Then you would need to test all the types to find out if it is a new type. — jdweng, Dec 10 '18 at 10:24
But i dont want to test the types. I want to avoid having to write `if(type == otherType)` — XomRng, Dec 10 '18 at 10:25
Are you looking for something along the lines of [this](https://stackoverflow.com/a/17680332/9363973)? — MindSwipe, Dec 10 '18 at 10:26
No MindSwipe, i know how to get derived types. My problem is that i want to programaticaly create expressions for Cat, Dog, Mouse classes from a single expression made for Animal class. So i could query EF without having to write those expressions explicitly — XomRng, Dec 10 '18 at 10:34

score 1 · Accepted Answer · answered Dec 10 '18 at 10:36

I think you should have a look at your data design again. It is here you can solve a lot of your problems.

You can use a SQL SERVER VIEW to return your animals from different tables, using a UNION from the relevant tables. Adding a new animal table requires a new UNION in the view.

You can also perform the same using a stored procedure. I would use one for getting the animals and one for saving an animal to ensure the correct table is updated.

You can store all animals in one table and have an AnimalType field that links to another simple table with ID and AnimalTypeName. Adding new animals is then simple.

There is also Entity Framework inheritance with tables to consider https://weblogs.asp.net/manavi/inheritance-mapping-strategies-with-entity-framework-code-first-ctp5-part-2-table-per-type-tpt

This sounds very good, i was under the false impression that i should be doing all data aggregations with code first entity framework, but writing a view or a stored procedure might be preferable here. Ill give this thread more time to see if anything else comes up, if not this will be accepted answer. — XomRng, Dec 10 '18 at 10:44

score 0 · Answer 2 · answered Dec 10 '18 at 10:40

0

A can suggest you an easier solution. Since in code you do have Animal as a parent, you could do the same for the database.

More exactly, instead of having different tables for Cats, Dogs, etc., have one table Animal(since the properties should be the same), with a property something like "AnimalType", an enum.

And from here queries would be very simple.

It's not the best solution, but it's a viable one.

answered Dec 10 '18 at 10:40

Cata Hotea

1,811
1
9
19

my Cats and Dogs also have a lot of properties that Animal class doesnt have and those properties also need to be stored in database. Wouldnt i have to create more tables to hold those properties? – XomRng Dec 10 '18 at 10:42
why not put them all together in the Animal class? its hard to believe that cats and dogs have some properties that other animals couldn't have – Cata Hotea Dec 10 '18 at 10:51

score 0 · Answer 3 · answered Dec 10 '18 at 16:35

I like to leverage the power of the database application rather than assume that C# code (for retrieving data) is more efficient. I also see it as a separation of concerns. It is the case that one requires some skill in setting up database tables (key, other indexes and so on) as well as views, stored procedures, etc.

Inheritance of objects in a database is not an uncommon scenario. You can go several ways about it. Which is best depends not least on how much rows and columns are involved and what you want to do with the data. Using your animals hierarchy as an example:- It is common in inheritance to push generalisation upwards and specialisation downwards, so we start by creating an "Animals" table. This has an integer field "Id" which is the key. We also have a field "Animal Name".

Now let's create a "Cat" table. And "Id" field is used but we have a foreign key "AnimalId" that links to the Animal table "Id". We add other fields common to cats. We do the same for mouse and other animals, pretty much as you have started.

Another way to achieve the above is to use one table for all animals. Now you need more columns for more animal types. This has a higher maintenance approach where changes might occur often, but works well where smaller, fixed ranges of objects are used. A simple way to retrieve some common data regardless of type is to use a computed column that outputs a varchar from the relevant fields for each object type.

Yet another way is to have a single animal table with all of the common properties. A second table stores Property Name/Value pairs. Each animal can now have any number of varying properties and it is simple to maintain from front-end code. This data structure has a simple, fixed design, yet still supports all you have requires so far. However, how useful this design is to you depends on how much analysis of the data is required. In the database you will have to flatten this row-based data into columns for some analysis processing.

Dynamicaly changing the type of a parameter in an Expression without knowing the target type at code time

3 Answers3