Independent subqueries in Linq SQL statement (DB only)

Question

Something similar to:

SELECT (SELECT COUNT(*) from Table1),(SELECT COUNT(*) from Table2 ) 

How to write it to linq? Or is it simply impossible?

Limitations

• You can hit the database only once:

    var result = new { Sum1 = db.Table1.Count(), Sum2 = db.Table2.Count() }); // is not valid..... 

• I do not want to use something similar (using the "help" table):

   var result = (from t3 in db.Table3 select new { Sum1 = db.Table1.Count(), Sum2 = db.Table2.Count() }).firstOrDefault(); //In order to get only the first row //but it will not return nothing if the table 3 has no entries...... 

• Do not use db.Database.ExecuteSqlCommand

Answer 1

I do not see a solution that solves all your limitations. This is one of the caveats using ORM-mapper, you do not control the generated SQL.

In this case, if it is completely unacceptable for you to send several queries to the database, the harsh truth is that you have to write the query yourself.

Update

I became curious and created an extension method that can do this! Of course, he creates his own SQL command, and it just works for Linq2SQL. Also a massive disclaimer: this is pretty dirty code, if I have time, I will fix it on the weekend :)

 public static TOut CountMany<TContext, TOut>(this TContext db, Expression<Func<TContext, TOut>> tableSelector) where TContext: DataContext { var newExpression = (NewExpression) tableSelector.Body; var tables = newExpression.Arguments.OfType<MethodCallExpression>() .SelectMany(mce => mce.Arguments.OfType<MemberExpression>()) .ToList(); var command = new string[tables.Count]; for(var i = 0; i < tables.Count; i++) { var table = tables[i]; var tableType = ((PropertyInfo) table.Member).PropertyType.GetGenericArguments()[0]; var tableName = tableType.GetCustomAttribute<TableAttribute>().Name; command[i] = string.Format("(SELECT COUNT(*) FROM {0}) AS T{1}", tableName, i); } var dbCommand = db.Connection.CreateCommand(); dbCommand.CommandText = string.Format("SELECT {0}", String.Join(",", command)); db.Connection.Open(); IDataRecord result; try { result = dbCommand.ExecuteReader().OfType<IDataRecord>().First(); } finally { db.Connection.Close(); } var results = new object[tables.Count]; for (var i = 0; i < tables.Count; i++) results[i] = result.GetInt32(i); var ctor = typeof(TOut).GetConstructor(Enumerable.Repeat(typeof(int), tables.Count).ToArray()); return (TOut) ctor.Invoke(results); } 

The code is called like this:

 var counts = dbContext.CountMany(db => new { table1Count = db.Table1.Count(), table2Count = db.Table2.Count() //etc. });