Getting data from multiple query without using foreach

Question

Getting data from multiple query without using foreach

.net dapper sql

Question

I am using the below code to query the count of the table in the table list.

List<string> tablelist = //some value
foreach( string table in tablelist)
{
var rowcount = string.Format("select count(*) from " + view + " WITH (NOLOCK)");
int countresult = con.Query<int>(rowcount).FirstOrDefault();
if(countresult >0)
{ //someoperation }
}

Here the call to DB is made every time. IS there any option where I can call DB only once and get the output of all the select statements in a single variable. Please help am new to dapper.net. How can I achieve this

Manjari

View more on Stack Overflow

G Davison · Accepted Answer · 8/18/2016 12:42:53 PM

Dapper's parameter expansion code doesn't handle IN queries with Lists however DapperExtensions' predicate system does allow IN queries and will convert an equals query predicate to a IN query if the parameter is a list type.

If you want to stick with Dapper for mapping, you can extend Jonathan Willcock's sysobjects/sysindexes query as a view and create a class to represent the results. You could then assemble a DapperExtensions GetList query with the appropriate parameters

View Creation:

CREATE VIEW TableRowCounts
AS
SELECT t.name TableName, i.rows Records 
FROM sysobjects t INNER JOIN sysindexes i ON i.ID = t.ID where t.xtype = 'U' and i.indid in (0,1)

Class for Results:

public class TableRowCounts
{
    public string TableName { get; set; }
    public int Records { get; set; }
}

DapperExtensions query to hydrate the results:

PredicateGroup query = new PredicateGroup();
List<string> tables;

tables = new List<string> { "table1", "table2" };
query.Predicates = new List<IPredicate>();
query.Predicates.Add(Predicates.Field<TableRowCounts>(f => f.TableName, Operator.Eq, tables));

List<TableRowCounts> results;
results = _connection.GetList<TableRowCounts>(query).ToList();

As a side note regarding IN performance, you may see performance drop if your list of parameters (table names in this instance) is greater than around 200 entries.

Getting data from multiple query without using foreach

Question

Fastest Dapper Plus Extensions

Popular Answer

Performance

Expression Evaluator