2

I'm using Dynamic Linq as the backend to an in-app reporting tool and I've hit a problem that I can't get round where I have to access a table that has a 1:M relationship .

My simplified data structure is this:

Database schema

If I were querying this in standard Linq I'd write the query as:

from a in context.Table_A
select new 
{
    a.RefNo,
    val = from b in a.Table_B
          where (b.A_ID == a.ID)
          where (b.code == "A0001"
          select(b.Value).FirstOrDefault()
}

This works without any problem. However, when I try the query using Dynamic Linq I can't get the join to work.

From the code below you can see what I'm getting at but obviously I can't use the "a." and the "a.Table_B" references in the query. What do I have to do to be able to access Table_B in this context?

string select =  "new (Ref_No, 
                       val = from b in a.Table_B
                       where (b.A_ID == a.ID)
                       where (b.code == \"A0001\"
                       select(b.Value).FirstOrDefault()";

var results = context.Table_A.Select(select);

Edit 1:

To answer @Hogan's comment - Why don't I use join: The reports system is dynamic and the select statement may or may not be joining on to Table_B (or indeed joining on to Table_B multiple times) so the join has to be optional. My other issue with this is that unlike the Select method where I can pass in a string as a parameter (allowing me to make it dynamic quite easily) the Join() method can't be called in that way. The closest thing I've found is a dynamic Linq join extention method, something I may have to consider using but I've a feeling that this will be cumbersome with the dynamic select().

Edit 2:

Based on Hogan's suggestions I've got this far:

delegate string searchTableA(Table_A a);

public void Search()
{
   ....

   searchTableA sel = (a) =>
   {
      return (from b in context.Table_B
      where (b.A_ID == a.ID)
      select (b.Value)).FirstOrDefault();
   };

   var res = context.Table_A.Select(sel);
}

This gives the error: 'System.Data.Entity.DbSet<TestDynamicLinqJoins.Table_A>' does not contain a definition for 'Select' and the best extension method overload 'System.Linq.Dynamic.DynamicQueryable.Select(System.Linq.IQueryable, string, params object[])' has some invalid arguments

Community
  • 1
  • 1
GrandMasterFlush
  • 6,269
  • 19
  • 81
  • 104

1 Answers1

0

Hard to give exact code because I don't know the types of your elements, but something like this would work fine using delegates.

delegate string searchTableA(elementType a);

searchTableA sel = (a) => 
  {
     return from b in a.Table_B
                   where (b.A_ID == a.ID)
                   where (b.code == "A0001")
                   select(b.Value).FirstOrDefault();
  };

var results = context.Table_A.Select(sel);
Hogan
  • 69,564
  • 10
  • 76
  • 117
  • @GrandMasterFlush - I can't help unless I can see more of the code. You are posting one line which means nothing. Please modify the question to include new source with the question or create a new question with more detail. – Hogan Jul 11 '13 at 15:19
  • Thanks for taking a look, I've updated the question with the current code. – GrandMasterFlush Jul 12 '13 at 08:51
  • 1
    @GrandMasterFlush - I see, you don't want `searchTableA(Table_A a)` to take a table, you want it to take an element of `Table_A`... a row or whatever `Table_A` enumerates over. – Hogan Jul 12 '13 at 14:31