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

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.

From stackoverflow

• 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...

  yapiskan : for my ignorance; what is single table discriminated inheritance that is supported by linq-to-sql.

  Marc Gravell : (updated example, btw) - where the table has a column such as "CustType", and the object-type is determined by that column. Like so: http://www.davidhayden.com/blog/dave/archive/2007/10/26/LINQToSQLInheritanceDiscriminatorColumnExampleInheritanceMappingTutorial.aspx

  yapiskan : Hmm, I don't know if I missed anything but I've already developed an application by using multiple table inheritance without using discriminatior by kind of POCO.

  yapiskan : To clarify; there was a base entity that includes base functionalities, and all other entities derived from that base one. By using that I made a generic method to get a single row from any entity.

  Marc Gravell : Very interesting... Out of curiosity, are you using attributed meta-models, or external (xml) meta-models?

  yapiskan : I've used attributed meta-models.

  Marc Gravell : Manually created classes, or dbml-generated? I'm simply curious? (and I'm trying a few things too ;-p)

  yapiskan : Manually created classes :)

  Marc Gravell : Ahhh... the dbml hates it, but that is neat.

  yapiskan : Hmm, I pointed that I've used kind of POCO. I knew that in POCO you have to create your classes manually. But sure, dbml could hate it.

• 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.

  Marc Gravell : Cheers for the info (in comments to my post); +1