none
Help for LINQ QUERY (INSERT MAX + 1) RRS feed

  • Question

  • I would like to replace single ADO call (below) with LINQ but I don’t want to create more than one query on SQL Server. Is this posible ?

     

    string insertCommand =

        "INSERT INTO Table_1 (Col_1, Col_2, Col_3) " +

        "OUTPUT inserted.Col_1 " +

        "VALUES((SELECT ISNULL(MAX(Col_1),0) FROM Table_1) + 1, @Col_2, @Col_3)";

     

    Description:

    Col_1 is not identity. It is an integer that I’m incrementing for every insert. (It is a PK so LINQ works).

     

    My current LINQ code has to many trips to database:

    1. Is Count > 0
    2. If Count > 0 find Max (Can I at least merge 1 & 2 into one step ?)
    3. Insert new values and for Col_1 use Max + 1

    Thanks,

    Vlad

     

    Friday, June 13, 2008 9:15 PM

Answers

All replies

  • You can always use DataContext.ExecuteCommand( ) -- see http://msdn.microsoft.com/en-us/library/system.data.linq.datacontext.executecommand.aspx

     

    Using stored procedures is another option.

     

    Thanks,

     

    --Samir

     

     

    Friday, June 13, 2008 9:59 PM
  • If you are asking a sort of out of the box batching, it is not there. The suggestion above is a work around to the issue.

    Friday, June 13, 2008 10:28 PM
  • Thank you for suggestions. I don't mind to keep ADO for inserting if there is no fast solution for LINQ.

     

    So I'll reduce my request to merge at least first 2 steps ?

     

    My current problem is that LINQ  (select ...).Max() will crash on empty table so I have first step to find out is table empty.

     

    Can I replace at least part of ADO query “(SELECT ISNULL(MAX(Col_1),0) FROM Table_1) + 1” with one LINQ trip to database ?

     

    If I can't is there better way (somthing like EXISTS) to check is table empty ? Count() > 0 is probably bad idea.

     

    Thanks,
    Vlad

    Saturday, June 14, 2008 6:41 PM
  • There is an "Any" that replaces "Exists", but why is count a bad idea ? It is actually faster than exists.

     

    Monday, June 16, 2008 5:32 AM
  • Starting from SQL 2005 execution plan for (if exists) and (if count > 0) is the same. In SQL 2000 difference was huge for big tables.

     

    In LINQ (if count > 0)  is not optimized and it is executed as “SELCET COUNT …” (SQL Server Profiler). Probably not to expensive operation for indexed table but still EXISTS is better.

     

    I checked and “.Any()” is executed as “EXISTS” on Server. I like this better.

     

    Thanks,
    Vlad

    Monday, June 16, 2008 1:55 PM