In linq how dynamically pass table and column name

Question

I want to write an dynamic linq where i send table and column name this query return me the max row number of the table .

SELECT ISNULL(MAX(intProductCode) + 1, 1) AS intProductCode FROM tblProductInfo

Above is my T-SQL syntax.I want same output from the linq how to

If i write this bellow syntax get same out put but here i can not set table and column name

this.Context.tblProductInfos.Max(p=>p.intProductCode)

How to set table and column name on linq which return max row number of this table.

Answer 1

As far as I understand you don't really need any dynamic linq here.

You should just stop thinking in terms of SQL magic strings like table and column names. L2S allows you to write queries in strong type manner. Examine the code you have already written.

this.Context.tblProductInfos.Max(p=>p.intProductCode)

1. tblProductInfos is actually a strong typed 'table name'
2. p=>p.intProductCode is an expression to get 'column name'

Everything is nice and clear. No magic strings required. That's what ORM for.

Answer 2

LINQ isn't the answer to everything. Sure, you can query the data-context to get all this information, but you're going to be doing a lot of work (either reflection or mapping, plus building an Expression), simply for the sql generator to reverse everything to get back TSQL. It would be a lot simpler to just build the TSQL (making sure to white-list the table/column names to prevent injection) and use ExecuteQuery.

For example:

string tsql = string.Format(
      "SELECT ISNULL(MAX([{0}]) + 1, 1) AS [{0}] FROM [{1}]",
       colName, tableName);

int max = dataContext.ExecuteQuery<int>(tsql).First();

Also; if intProductCode is the IDENTITY, you might want to look at IDENT_CURRENT(tableName), which is much more efficient than MAX.

Answer 3

Here you go:

int GetMax<T>(DataContext dc, Expression<Func<T, int>> selector)
{
  int result = dc.GetTable<T>().Max(selector);
  return result;
}

Called like this:

int maxId = GetMax<Customer>(myDC, c => c.CustomerId);

---

On the other hand, you could just use IDENTITY columns in the database. Then you don't have to +1, and you don't have to deal with collisions from multiple connections.