Can I make a generic CompiledQuery that accepts multiple tables and generic types?

Question

Hello,

I have multiple tables with an "id" component. I'd like to use LINQ to get an item from one of these tables with the correct LINQ-To-SQL type, but only using one CompiledQuery.

Here's an example that works currently. Say the object type defined in the DBML is "myitem" and its table is "myitems".

    var getOneItem = CompiledQuery.Compile(
        (MyDataContext db, long id) => ((from i in db.myitems
                                              where i.id == id
                                              select i).SingleOrDefault()));

I'd like to make this, where the type can be "myitem", "myitems2", or whatever table and object type I might be interested in.

    var getOneItemAnyTable = CompiledQuery.Compile(
        (Table tab, Type T long id) => ((from anItem in tab<T>
                                              where anItem.id == id
                                              select anItem).SingleOrDefault()));

I made up this syntax because I'm not sure what the syntax should look like here. Any ideas if it's possible? Would I have to munge the DBML to make some kind of superclass for the types involved?

Thank you.

Answer 1

I'm pretty sure you can't make the one compiled-query span multiple tables (if that is what you mean); how would it know where to look? EF supports something like this with inheritance (and multiple tables), but even with inheritance LINQ-to-SQL only supports single-table discriminated inheritance.

The heart of this question is largely identical to this one from earlier today. The tricky bit is the primary key, since that is hard to define abstractly. The rest is just GetTable<T> (with LINQ-to-SQL at least).

You don't need a superclass in this case - just a way to get the primary key. Fortunately, I seem to recall that LINQ-to-SQL offers a way to do this (without needing to use the attributes, which is not a pre-req of LINQ-to-SQL); the following doesn't pre-compile (and is untested), but should be fairly close:

(update fixed and tested)

Usage first:

Foo foo = ctx.Get<Foo>(fooid);
Bar bar = ctx.Get<Bar>(barid);

code:

    public static TItem Get<TItem, TKey>(
        this DataContext ctx, TKey key)
        where TItem : class
    {
        var table = ctx.GetTable<TItem>();
        var primaryKey = ctx.Mapping.GetMetaType(typeof(TItem))
                .DataMembers.Where(
            member => member.IsPrimaryKey).Single().Member.Name;

        var item = Expression.Parameter(typeof(TItem), "item");
        var lambda = Expression.Lambda<Func<TItem, bool>>(
            Expression.Equal(
                Expression.PropertyOrField(item, primaryKey),
                Expression.Constant(key, typeof(TKey))),
                item);
        return table.Single(lambda);
    }
    public static TItem Get<TItem>( // common case
        this DataContext ctx, int key)
        where TItem : class
    {
        return Get<TItem, int>(ctx, key);
    }
    public static TItem Get<TItem>( // common case
       this DataContext ctx, string key)
       where TItem : class
    {
        return Get<TItem, string>(ctx, key);
    }

I'll see if I can find a way to pre-compile it...

Answer 2

Instead of doing that, derive all your entities from a base entity that has common properties in your entities (like Id). Implementing these layer with POCO will help you.