score:3

Accepted answer

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"/>

score:0

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.

score:0

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.

score:0

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.

score:0

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.

score:0

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

score:0

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


Related Articles