none
Multiple Joins in Dynamic Query RRS feed

  • Question

  • Hello, I'm working on a simple Find Control in my Application that allows the user to select filters on querying data from DB. So I need Linq's Dyamic Queries. The sample given in demo projects of VS 2008 is really too simple, it's just filter in the Where method some fields in asingle table. In my case I have to make multiple joins to get all info involved in search criteria. For example I have to find all requests submited by a customer which user types initial char of the name, and beetween two dates. In my DB customers are People or Companies so I have 4 entities: Request, Customer, Person and Company. I need to join these 4 tabels to search all request submitted by a person or a company: actually, is an inner join between Request and Customer and left outer joins with Person and Company.
    By following the example i should use Extended methods to set entities to be joined and set the dynamic Where method like .Where("criteria", parameters);
    Now, my questions are:

    1) Which is the best way to build and execute dynamically join-equiped queries
    2) if I should use Extension methods (Join or GroupJoin) which is the correct way to accomplish this task

    I figure out a method that is not elegant:

    var q = Customers.GroupJoin(Persons, c => c.ID, p => p.ID, (x, y) => new { Customer=x, Persons=y })
                .SelectMany(x => x.Persons.DefaultIfEmpty(), (x, y) => new { Customer=x.Customer, Person=y})
                .GroupJoin(Companies, c => c.Customer.ID, w => w.ID, (x, y) => new { Customer=x, Companies=y })
                .SelectMany(x => x.Companies.DefaultIfEmpty(), (x, y) => new { Customer=x.Person, Company=y});

    foreach(var cust in q)
        {
            Console.WriteLine("{0} - {1}",
            cust .Customer.Customer.ID,
            cust .Customer.Person != null ? pers.Customer.Person.LastName + " " + pers.Customer.Person.FirstName : pers.Customer.Customer.Company.Name);
        }
            
    it works but I don't like that I have to join the result of previous joins: in second GroupJoin the outerkeyselector must be c.Customer.ID instead c.ID.

    3) Is there a way to execute a Linq expression build as string (like LinqPad does)?


    Thanks
    Alessandro
    Friday, October 23, 2009 1:51 PM

Answers

  • Hi Alessandro,

    You can use 'from ... in ... ' clause to make your code look 'elegant'.

    Well, 
               ' var results =
                           from a in db.tableA
                           join b in db.tableB on a.ID equals b.ID
                           into g
                           ...'

    is the form of 'inner join' while

               ' var results =
                           from a in db.tableA
                           join b in db.tableB on a.ID equals b.ID into temp
                           from t in temp.DefaultIfEmpty()
                           select new {a,b};
                           '
    is the form of 'left outer join'.

    There is a way to execute a Linq expression build as string, but you have to write your own provider and parse the string. It's really complex and costs much time. So we do not suggest that way. However, you can use the 'ExecuteQuery' method to execute the SQL like a string when it's needed. Or you can use the 'Dynamic Expression API' to do SQL-like queries.

    You can refer to the following links:

    http://msdn.microsoft.com/en-us/library/bb361109.aspx
    http://weblogs.asp.net/rajbk/archive/2007/09/18/dynamic-string-based-queries-in-linq.aspx
    Best regards,
    Charlie Lee

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Zhipeng Lee Thursday, October 29, 2009 2:30 AM
    Monday, October 26, 2009 9:48 AM