Databases
StackOverflow2013
Postsdbo
PO
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    text
    copied!<p>I cannot see a solution which solves all your limitations. This is one of the caveats with using an ORM-mapper, you are not in control of the generated SQL. </p> <p>In this case, if it is utterly unacceptable for you to send more than one query to the database, the harsh truth is that you will have to write the query yourself.</p> <p><strong>Update</strong></p> <p>I got curious and created an extension method that can do this! Of course it constructs its own SQL command, and it just works for Linq2SQL. Also massive disclaimer: It's fairly dirty code, if I have some time I'll fix it up in the weekend :)</p> <pre><code>public static TOut CountMany&lt;TContext, TOut&gt;(this TContext db, Expression&lt;Func&lt;TContext, TOut&gt;&gt; tableSelector) where TContext: DataContext { var newExpression = (NewExpression) tableSelector.Body; var tables = newExpression.Arguments.OfType&lt;MethodCallExpression&gt;() .SelectMany(mce =&gt; mce.Arguments.OfType&lt;MemberExpression&gt;()) .ToList(); var command = new string[tables.Count]; for(var i = 0; i &lt; tables.Count; i++) { var table = tables[i]; var tableType = ((PropertyInfo) table.Member).PropertyType.GetGenericArguments()[0]; var tableName = tableType.GetCustomAttribute&lt;TableAttribute&gt;().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&lt;IDataRecord&gt;().First(); } finally { db.Connection.Close(); } var results = new object[tables.Count]; for (var i = 0; i &lt; 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); } </code></pre> <p>the code is called like this:</p> <pre><code>var counts = dbContext.CountMany(db =&gt; new { table1Count = db.Table1.Count(), table2Count = db.Table2.Count() //etc. }); </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload