What are the ways to perform these using LINQ?

Question

Though I liked LINQ very much and using in our current project, sometimes we are facing problem to solve the following.

1. Returning multiple results (tables) with single DB call from database and binding the same in ASP.NET.
2. In case of stored procedures, using single SP we can perform multiple operations with single DB call. Using LINQ, I think, we need to send multiple requests to DB to perform multiple operations. Of course, in this case we can use LINQ to SQL but is there any other way to do this?

Answer 1

If you are using Linq to SQL to query multiple databases then it will construct a single query to the database and execute that, unless you access Linq objects in between adding aspects of the query. You can also call stored procedures using Linq to SQL so I don't see any issues.

You can experiment with queries using LinqPad and within the tool see the generated SQL statement that's used to query the database. You'll be surprised at how economical Linq to SQL is.

Answer 2

I've found with LINQ that you're best bet is generally to return anonymous objects and compose them into real objects that you can return to the UI. Generally trying to include a bunch of other tables with your single select is going to perform poorly because the joins end up being quite large.

var users = (from p in db.Users
                         select new
                         {
                             p.aspnet_Users.UserName,
                             p.Area, 
                             p.firstName,
                             p.lastName
                         });

Answer 3

1st:

I never did this, but I do believe that LINQ can accept multiple tables. You just cannot use code autogeneration.

In autogenerated code you can see something like this:

IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), date, city, state, country);
            return ((ISingleResult<GetCityVisitTotalResult>)(result.ReturnValue));

It worth to try to cast it to IMultipleResults (even with small sample): http://msdn.microsoft.com/en-us/library/system.data.linq.imultipleresults.aspx

Regarding 2nd:

In case when you really need to execute few SP in single call, you can create SP which execute sql, write code which execute your stored procedures :)

I endup with Stored procedures which accept list arguments. AS example, instead of 20 calls to SetViewsForContent @contentid I pass csv list to SetViewForContentList

In SQL I created function intlisttotable(string, separator) and than it is easy to handle it.