How do I resolve multiple linq queries in one operation without using a base query?

Question

I have 14 LINQ queries to resolve in one method. None of them have a base query that I could hang them from as subqueries and then store the results as properties of an anonymous type instance.

Rather than making 14 separate calls to the database, how can I ensure that they are all called in the same operation?

UPDATE

I ended up using a dodgy hack which did the trick. I know that there will ALWAYS be at least one user in the user table, so I ended up using:

var data = (from tmp in DataContext.Users
            select new {
                Property1 = (from...),
                Property2 = (from...),
                PropertyN = (from...),
            }).First();

UPDATE 2

It has been implied in one of the answers that doing this may create MARS (Multiple Active Result Set) errors. What are these errors (I have never seen one), why do they occur and is there anything wrong the whole premise of this line of questioning? i.e. Am I flawed in my assertion that asking the database to return everything in one go is going to be quicker/more efficient than doing fourteen wholly separate LINQ queries?

UPDATE 3

I think my approach is redundant and from a pragmatic perspective, should be handled differently, either via a stored procedure or some sort of delayed/mixed approach.

Answer 1

I'm not completely sure about your queries but you could encapsulate the query logic in a stored procedure drag it with LINQ to SQL data context designer to use.

If you need the results independently and you really don't want the round trip to database server, I think you should fall back to SqlDataReader and use NextResult by calling a procedure that retrieves the whole data.

Answer 2

You're still making 14 seperate call's to the database through each of your sub queries, your just doing it at a single instance when your data object is called.

Edit:

The MARS error occurs when you have multiple open data readers for a connection. By default with SQl 2005 this is turned off. in your query, while all the SQL is being passed in one hit your still returning 14 datareaders. The datareader takes that connection exclusivly unless you tell the connection to allow multiple active result sets (MARS).

To resolve this you either need to preload each of your sub queries into a list and run your subqueries off of that list, or you need to set the MutlipleActiveResultSet attribute of the connection string = true.

<add name="Name" connectionString="Data Source=<server>;Initial Catalog=<database>;Integrated Security=True;MultipleActiveResultSets=true" providerName="System.Data.SqlClient"/>

Answer 3

You may take the SQL code for each query and then send it to the DB in a single batch and finally iterating through the 14 result sets.

Answer 4

Nathan, you say the queries are not necessarily inter-dependents but are all related. What we need to know is if they are effectively 14 different result sets or it could be combined in one single result set. If the latter, why can't you create a view or stored procedure for this purpose? In some situations you need to use judgment to discern when to use other routes for a better performance and get the job done. Mixing approaches is not bad at all.

If you need 14 different result sets you could use Multiple Active Result Sets (MARS), by enabling it in the the connection string. This way they would be done in a single round trip.

Answer 5

If you rework your 'dodgy hack' it should run the queries simulateneously:

var data = from a in (from x in ... select x)
           from b in (from x in ... select y)
           from c in (from x in ... select z)
           select new { A = a, B = b, C = c };
var result = data.First();

However, it will do a full join on all items, which is probably not what you would like to occur. Using IQueryable.Concat may work if the queries all return the same number of columns:

var data = (from x in ... select new { Part = 1, Val = x })
    .AsQueryable() // may not be needed in your context
    .Concat(
        (from y in ... select new { Part = 2, Val = y })
            .AsQueryable()
            .Concat(
                (from z in ... select new { Part = 3, Val = z }).AsQueryable()
            )
    );
foreach (result in data)
{
    switch (result.Part)
    {
        // process each query's resultset
    }
}

Still dodgy (and untested), but should only hit the DB once.

Answer 6

hey Ridely

I'm not sure doing the queries off the Users table is really helping you at all.

Before writing the LINQ queries, try to think about how you would achieve your goal using basic SQL.

I'n not sure what you're trying to do exactly because you haven't provided much code, but, if you were to do this in a Stored Procedure, what would that look like, and what would it return?

I'm asking this because maybe the data structure you need just doesn't "fit" into a single query.

If it does, then you just need to rethink you're whole LINQ approach.

maybe you could provide more info on what Data you're actually trying to retrieve, and how?

cheers

Answer 7

Not sure if this helps, but Wondering why you wouldn't use a let keyword to enhance the query.

'let' allows you to run subqueries and store the results in a temp variable, which you can then further use in the master query