locked
Linq to Sql Trickery - How can I make this generic? RRS feed

  • Question

  • Evening All,

    I am hoping you may be able to shed some light on a situation I am having. I have various entities generated via linq to sql.

    Many of them have an identifier field of varchar(8). Format is as follows “00000001”, “00000002” etc. This is a current limitation of the existing dataset which I cannot change.

    By way of example, let’s say I have two tables / entities:

    Person {PersonId, Name, DOB}
    Dog {DogId, Name, DOB}

    Both PersonId and DogId are primary keys with the above format.

    At the moment when I insert a Person I need this:

    Public string GenerateId()
    {
         ...Context.Person.Max(o => Convert.ToInt32(o.PersonId))+1).ToString().PadLeft(8, ‘0’);
    }

    Which gives me a padded string representation of the next ID, then I can SubmitChanges(). Then, id have to do the same thing for DogId:

    Public string GenerateId()
    {
         ...Context.Dog.Max(o => Convert.ToInt32(o.DogId))+1).ToString().PadLeft(8, ‘0’);
    }

    Every new table I need to generate an Id in this format would need another GenerateId() to do so.

    How feasible is it to have something generic which will do this? Maybe the usage would look like this:

    GenerateId(...Context.Person, “PersonId”);

    For the signature:

    GenerateId(Table<object> _table, string ObjectId);

    Would be greatful if some of you great minds could have a think about this for me, as my head hurts!

     

    Cheers

    Shai

     

    Wednesday, March 24, 2010 7:37 AM

Answers

  • Hello,

     

    Welcome to LINQ to SQL forum!

     

    Based on my understanding, you may want such a generic method to dynamically create the Lambda Expressions or Expression Trees of “o => Convert.ToInt32(o.DogId) + 1” and call the DataContext.Dogs.Max(…) method:

    ==============================================================================

            private string GenerateId<T>(IQueryable<T> source, string propertyName)

            {

                Type type = typeof(T);

                ParameterExpression param = Expression.Parameter(type, "type");

                MemberExpression member = Expression.MakeMemberAccess(param, type.GetProperty(propertyName));

     

                MethodInfo toInt = typeof(Convert).GetMethod("ToInt32", new Type[] { typeof(string) });

                MethodCallExpression methodCall = Expression.Call(toInt, member);

     

                BinaryExpression add = Expression.Add(methodCall, Expression.Constant(1));

     

                Expression<Func<T, int>> lambda = Expression.Lambda<Func<T, int>>(add, param);

     

                return source.Max(lambda).ToString().PadLeft(8, '0');

            }

     

    var id = GenerateId(context.Dogs, "DogId");

    ==============================================================================

    Most of the classes to represent the Expression Tree elements can be found under the namespace System.Linq.Expressions.

     

    Besides, I would recommend you a tool to troubleshoot the Expression Tree Visualizer, http://msdn.microsoft.com/en-us/library/bb397975.aspx.

     

    If you have any questions, please feel free to let me know.

     

    Have a great day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by shai_au Sunday, March 28, 2010 9:33 PM
    Thursday, March 25, 2010 3:10 AM

All replies

  • Hello,

     

    Welcome to LINQ to SQL forum!

     

    Based on my understanding, you may want such a generic method to dynamically create the Lambda Expressions or Expression Trees of “o => Convert.ToInt32(o.DogId) + 1” and call the DataContext.Dogs.Max(…) method:

    ==============================================================================

            private string GenerateId<T>(IQueryable<T> source, string propertyName)

            {

                Type type = typeof(T);

                ParameterExpression param = Expression.Parameter(type, "type");

                MemberExpression member = Expression.MakeMemberAccess(param, type.GetProperty(propertyName));

     

                MethodInfo toInt = typeof(Convert).GetMethod("ToInt32", new Type[] { typeof(string) });

                MethodCallExpression methodCall = Expression.Call(toInt, member);

     

                BinaryExpression add = Expression.Add(methodCall, Expression.Constant(1));

     

                Expression<Func<T, int>> lambda = Expression.Lambda<Func<T, int>>(add, param);

     

                return source.Max(lambda).ToString().PadLeft(8, '0');

            }

     

    var id = GenerateId(context.Dogs, "DogId");

    ==============================================================================

    Most of the classes to represent the Expression Tree elements can be found under the namespace System.Linq.Expressions.

     

    Besides, I would recommend you a tool to troubleshoot the Expression Tree Visualizer, http://msdn.microsoft.com/en-us/library/bb397975.aspx.

     

    If you have any questions, please feel free to let me know.

     

    Have a great day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by shai_au Sunday, March 28, 2010 9:33 PM
    Thursday, March 25, 2010 3:10 AM
  • I've been watching this thread with interest to find what trickery can be done and, as almost always, LINQ manages to provide a solution. However I would worry about performance of the approach and also potential risk that you could end up with duplicate id values for the same table.

    Firstly the performance: is using Max() a peformant way of getting the current highest, especially if the table grows large?

    Secondly the chance of duplicates: If you have a single process with a single thread in a small database, then there isn't really a problem with the approach. But if the new values can be generated by multiple threads or multiple processes concurrently, then two could yield the same value at the same time.

    I would be inclined to add a new table to the database with two columns (e.g. object_name and object_id) which can be used to hold the highest id for the particular object. You then have the chance to

    1. start a transaction
    2. read the current value from the new table, locking that record to stop others reading it
    3. calculate the new value
    4. write the new value back to the new table.
    5. commit the transaction which unlocks the record, allowing other threads/processed to proceed.

    You can use the new value in other tables within the transaction, either before or after #4. That depends on whether you want the id values to be monotonically increasing or not.

    You have to watch out with LINQ's optimistic locking though as there is no easy way to force the record lock in #2. I have recently had to overcome a similar problem recently which would have been more manageable in SQL (some of them documented here - http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/fa84abd7-730d-4d30-a383-5d73c3c9d8d6).

    An alternative would be to perform steps #2, #3 and #4 in a neat stored procedure, callable from LINQ.

    Thursday, March 25, 2010 10:07 AM
  • To avoid unnecessary roundtrips, locking, transactions where otherwise not needed, and duplicate issues (as mentioned in John T.Angle's post above), I would probably choose to do this with a default constraint in the database instead...

    E.g.:

    create function fn_my_test_next_key() returns varchar(8)
    as 
    begin
     declare @retval varchar(8);
     select @retval = convert(varchar(8), coalesce(convert(int, MAX(id)), 0)+1) from my_test;
     set @retval = replicate('0', 8-len(@retval)) + @retval;
     return @retval
    end
    
    create table my_test (id varchar(8) not null default dbo.fn_my_test_next_key(), foo nvarchar(255), constraint pk_my_test primary key (id));
    

     

    ...just my 2 cents... :)


    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for the L2S and EF designers in VS2008 and VS2010)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    Thursday, March 25, 2010 11:07 AM
    Answerer
  • Thank you all for your input, and to Lingzhi for a potential solution.

    I had considered the various constraints with this approach before posting, but you all make valid points for a large, multiuser, high transaction environment.

    Regards,

    Shai

    Sunday, March 28, 2010 9:36 PM