c# - Independent subqueries in SQL to Linq statement (hit DB only one time) -

having similar to:

 select (select count(*) table1),(select count(*) table2 ) 

how write in linq? or simple not possible?

limitations:

• can hit database 1 time:

     var result = new {                  sum1 = db.table1.count(),                  sum2 = db.table2.count()                 });  // not valid..... 

• i not want use similar (using "helping" table):

  var result = (from t3 in db.table3              select new {                  sum1 = db.table1.count(),                  sum2 = db.table2.count()              }).firstordefault();  //in order first row   //but not return nothing if table 3 has no entries...... 

• not using db.database.executesqlcommand

i cannot see solution solves limitations. 1 of caveats using orm-mapper, not in control of generated sql.

in case, if utterly unacceptable send more 1 query database, harsh truth have write query yourself.

update

i got curious , created extension method can this! of course constructs own sql command, , works linq2sql. massive disclaimer: it's dirty code, if have time i'll fix in weekend :)

public static tout countmany<tcontext, tout>(this tcontext db, expression<func<tcontext, tout>> tableselector)         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 = 0; < 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(*) {0}) 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();         }                 {             db.connection.close();         }          var results = new object[tables.count];         (var = 0; < 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 called this:

var counts = dbcontext.countmany(db => new                                         {                                            table1count = db.table1.count(),                                            table2count = db.table2.count()                                            //etc.                                        });