EntityFramework, how to decide which dbSet to use with switch/case?

Question

I'm a beginner with EntityFramework and WCF, so I don't yet know how everything works, so I'll try to explain my situation as best as I can..

I have WCF service that uses a database with EntityFramework and I have managed to get it to work just fine for example like this:

using (var entities = new databaseEntities())
{
    // Check if there is 0 rows, then just add the new row.
    int count = entities.Table1.Where(i => i.Name == name).Count();

    if (count < 1)
    {
        var newEntry = new Table1
        {
            Name = name,
            InsertDate = DateTime.Now,
            CreatedBy = createdBy,
            Comment = comment,
            Active = true
        };

        entities.Table1.Add(newEntry);
        entities.SaveChanges();
     }
}

The problem arrives when I have more than one table and I want to decide which one to use. The tables are basically the same and thus would use the same operations, so I would like to use one function for all of them (that way I can avoid duplicate code). But I can't seem to understand how I can change the table at runtime for example via a switch/case.

For Example:

// A function that gets the type of the table I want to access
void WriteToSomeTable(int type)
{
    switch (type)
    {
        case 0:
            //The table to update is Table1
            break;
        case 1:
            //The table to update is Table2
            break;
     }
}

If I want to get the count of all the entries with a given name with

int count = entities.Table1.Where(i => i.Name == "somename").Count();

how can I make the "entities.Table1" determined at runtime? I can make variables:

System.Data.Entity.DbSet<Table1> firstTable = entities.Table1;
System.Data.Entity.DbSet<Table2> secondTable = entities.Table2;

So I thought that with a list i could set an int index; to a different value with the switch/case and then just use

int count = list[index].Where(i => i.Name == "somename").Count();

but I can't add them to a List, since they are different types

// entity.Table1 is
System.Data.Entity.DbSet<Table1>
// and entity.Table2 is
System.Data.Entity.DbSet<Table2>

ArrayList won't cut it either, since there are no ".Where" function if i try to use the objects inside the ArrayList.

I also tried with just the System.Data.Entity.Dbset, but to use the ".Where" function, I would need to use .Cast() function, but I cant store the needed "TEntity" to a variable (or can I?). For Example:

System.Data.Entity.DbSet firstTable = entity.Table1
Type t = firstTable.GetType();
int count = firstTable.Cast<t>().Where(i => i.Name == "somename").Count();//doesn't work
//This, however works:
int count = firstTable.Cast<Table1>().Where(i => i.Name == "somename").Count();

I hope I made some sense on what my problem here is :) Hopefully someone has an idea, how to solve this, since I have battled with this for ages now, and the only solution I have come up with is to have a separate function call in each switch/case with the exact same code, except for the "entity.Table" part. And having to write the same set of code multiple times isn't a very good solution :(

caner · Accepted Answer · 2017-04-25T12:38:40.957

3

Make generic function...

    public void MyAddFunction<T>(T newItem, databaseEntities db, 
           Expression<Func<T, bool>> predicate) where T : class
    {
        var table = db.Set<T>();

        int count = table.Count(predicate);

        if(count < 1)
        {
            table.Add(newItem);
        }

        db.SaveChanges();
    }

and according to your cases call your function(You want to add table1 for example)

using(var entities = new databaseEntities())
{
   MyAddFunction<Table1>(new Table1(), entities , x => x.Name == "name");
}

edited Apr 25 '17 at 12:38

answered Apr 25 '17 at 12:33

caner

721
5
21

Awesome! This was simple and it worked wondrously! I wasn't too familiar with generic functions, so this didn't even come to mind :) Thanks a bunch! – Juuseri Apr 27 '17 at 05:36
glad to help :) – caner Apr 27 '17 at 06:14
2

I'd use `Any` instead of `Count`. – Gert Arnold Apr 27 '17 at 08:13
You are totally right @Gert Arnold. I did not thought performance issues... – caner Apr 27 '17 at 08:23

score 1 · Answer 2 · answered Apr 25 '17 at 12:28

You don't say so, but since you are working with Entity Framework, I assume that your class databaseEntities is the DbContext that contains all your Entities as DbSet<TEntity> properties.

You write that your problem is that you know the type of the entities (in my exaple TEntity), but you don't know how to get the corresponding DbSet. Your proposal is to use a switch statement for this.

Luckily this is not needed. DbContext.Set(Type) does this for you. You provide the Type, Dbcontext returns the DbSet of this type.

public class SchoolContext : DbContext
{
    public DbSet<Student> Students {get; set;}
    public DbSet<Teacher> Teachers {get; set;}
    public DbSet<ClassRoom> ClassRooms {get; set;}
    ...
}

If you know the type already at compile time, use DbContext.Set<TEntity>, if TEntity is only known at runtime, use DbContext.Set(Type entityType)`

Type entityType = ...;
DbSet mySet = DbContext.Set(entityType);

The problem is of course, that at compile time you don't know the entityType, and thus don't know what functions you can call and what properties your entityType has.

If you are certain that your entities has certain properties, like in your example property Name, consider deriving all your entities from a common interface. Like this:

interface ICommonSchoolProperties
{
    public int Id {get;}           // primary key
    public string Name {get;}
}

public class Teacher : ICommonSchoolProperties {...}
public class ClassRoom : ICommonSchoolProperties {...}
etc.

Now you are certain, that whenever you ask the SchoolContext for an item of any type, you are certain that the items you get have at least an Id and a Name. And thus you can do the following:

Type entityType = ...
IEnumerable<ICommonSchoolProperties> schoolItems = mySchoolContext
    .Set(entityType)
    .Cast<ICommonSchoolProperties>)();

And you can call the functions of which you are certain that your SchoolProperty has.

var nrOfItems = schoolItems
    .Where(schoolItem => schoolItem.Name = ...)
    .Count();
var allKnownNames = schoolItems
    .Select(schoolItem => schoolItem.Name)
    .Distinct();

The problem remains if you want to call functions that Teachers have, but ClassRooms have not.

This is a fairly rare case, and if you have an object of which you don't know what functions it has, you should reconsider your design and think about creating functions that handle these objects, instead of giving the type, decoding what functions this object has and then use them

Instead of:

private void WriteToSomeTable(Type type)
{
    if type is a teacher
        call teacher functions
    else if type is classroom
        call classroomfunctions
    else

    DoSomeThingcommon(...)
}

Consider:

public void WriteTeacherTable()
{
    call teacher functions();
    DoSomethingCommon();
}

private void WriteClassRoomtable()
{
     call classroom functions;
     DoSomethingCommon();
}

Note that the number of lines hardly increase.

Somewhere inside your program there is a place where you know that you are dealing with Teachers instead of ClassRooms. The only reason in a proper OO design where you would mix Teachers and ClassRooms as if they were something similar would be if you know you only want to call the functions they have in common. If that is the case, go back to the interface function where you know which common functions you can call.

EntityFramework, how to decide which dbSet to use with switch/case?

2 Answers2