How can I query different tables dynamically with LINQ?

Question

So I'm not sure if you can do this or not, but I would like to avoid using SQL strings if I can. What I would like to do with Linq/DbContexts is the following that can be done easily with SQL:

string sql = "UPDATE " + tableName + " SET Status=0 WHERE Id=" + formId.ToString();

I can easily put this into a loop where tableName and formId are given dynamically and execute no problem.

I have multiple DbContexts, so I don't know of any way to do something like:

var db = new *dynamicallyChosenContext*()

var query = from p in db.*dynamicallyChosenAlso*
            where p.Id == formId
            select p;

foreach (var result in query)
{
    result.Status = 0;
}

db.SaveChanges()

Thanks for the help!

Answer 1

Here is a piece of working code that can update different tables at runtime from different contexts without using reflection.

namespace DemoContexts
{
    using System;
    using System.Collections.Generic;
    using System.Data.Entity;
    using System.Linq;


    public interface IThing
    {
        int Id { get; set; }
        int Status { get; set; }
    }

    public class FirstPersonThing : IThing
    {
        [System.ComponentModel.DataAnnotations.Key]
        public int Id { get; set; }
        public int Status { get; set; }
        public string Foo { get; set; }
    }

    public class SecondPersonThing : IThing
    {
        [System.ComponentModel.DataAnnotations.Key]
        public int Id { get; set; }
        public int Status { get; set; }
        public string Bar { get; set; }
    }

    public class FirstContext : DbContext
    {
        public FirstContext() : base("FirstContext") { }
        public DbSet<FirstPersonThing> MyThings { get; set; }
        public DbSet<SecondPersonThing> YourThings { get; set; }
    }

    public class SecondContext : DbContext
    {
        public SecondContext() : base("SecondContext") { }
        public DbSet<FirstPersonThing> MyThings { get; set; }
        public DbSet<SecondPersonThing> YourThings { get; set; }
    }


    class Program
    {
        static void Main(string[] args)
        {
            int contextType = 1;
            int thingType = 1;

            DbContext db = RunTimeCreatedContext(contextType);
            IQueryable<IThing> collection = RunTimeCreatedCollection(db, thingType);

            UpdateRuntimeDeterminedThings(db, collection, 1);

            Console.ReadLine();
        }

        public static void UpdateRuntimeDeterminedThings(DbContext db, 
                                               IQueryable<IThing> collection, 
                                               int formId)
        {
            var querySet = collection.Where(p => p.Id == formId).ToList();
            foreach (var result in querySet)
            {
                result.Status = 0;
            }

            db.SaveChanges();
        }

        static DbContext RunTimeCreatedContext(int contextType)
        {
            if (contextType == 0)
            {
                return new FirstContext();
            }
            else
            {
                return new SecondContext();
            }
        }

        static IQueryable<IThing> RunTimeCreatedCollection(DbContext db, int thingType)
        {
            if (thingType == 0)
            {
                return db.Set(typeof(FirstPersonThing)) as IQueryable<IThing>;
            }
            else
            {
                return db.Set<SecondPersonThing>();
            }
        }
    }
}

The first thing to note is that all this is statically typed so to perform a generic query against different types of objects these objects must have common property signatures and this conceptually is expressed in the IThing interface.

A second thing to note is how the IQueryable is generated. It is generated by the DbContext.Set Method (Type) in the first instance (for the FirstPersonThings), it is generated by the DbContext.Set<TEntity> Method in the second instance. The first uses a type determined at runtime and requires a cast (but could be useful to use passing types at runtime), the second uses generics and the types are determined at compile time. There are obviously a number of other ways that this function could work.

Finally the method UpdateRuntimeDeterminedThings works because it uses properties and methods that are shared across the types (either with base types/inheritance or by the implementation of interfaces).

None of this is actually dynamic programming (which is possible using the dynamic type) and I have used the term runtime determined rather than dynamic to describe how this works.

Answer 2

There is a technique in functional programming, called Currying, where you could pass as much parameters as you want, so you are able to access them. Here is an exemple: http://blogs.msdn.com/b/sriram/archive/2005/08/07/448722.aspx

P.S: You could use a currying function to iterate through yours DBContexts.

Answer 3

I think you have to use reflection if you want to use 'code' and not sql strings. That's just how C# works... This is how you could do it:

using System;
using System.Data.Entity;
using System.Linq;

public class TestContext : DbContext
{
    public DbSet<Box> Boxes { get; set; }
    public DbSet<Thing> Things { get; set; }
}

public abstract class Base
{
    public int Id { get; set; }
}

public class Box : Base
{
    public string Title { get; set; }
}

public class Thing : Base
{
    public string Name { get; set; }
}

internal class Program
{
    private static void Main(string[] args)
    {
        var db = new TestContext();

        DoIt(GetPropValue(db, "Boxes") as IQueryable<Base>);
        DoIt(GetPropValue(db, "Things") as IQueryable<Base>);
    }

    private static void DoIt(IQueryable<Base> b)
    {
        Console.WriteLine(
            b.Single(t => t.Id == 1).Id);
    }

    private static object GetPropValue(object src, string propName)
    {
        return src.GetType().GetProperty(propName).GetValue(src, null);
    }
}

Obviously you can then put the strings in a list etc, whatever you need.