none
LINQ to SQL with type-safe integer class RRS feed

  • Question

  • I have the following class designed to provide a type-safe mechanism to pass an Int64 into my business layer:

        class ClientID {
            public ClientID(long i) { Value = i; }
            ... other constructors
       
            public long Value { get; private set; }
       
            public static bool operator ==(ClientID lhs, long rhs) { return lhs.Value == rhs; }
        }
    
    


    There are a few other comparison operators, plus some Object overrides. This allows me to write LINQ code that says:

        ClientID searchID = something;
       
        from i in someSQLdatasource
        where i.ClientID == searchID
        select i;
    
    
    


    The syntax works, but of course LINQ to SQL cannot generate the SQL code for my ClientID class. I could explicitly reference the .Value property, but I think the syntax I used is more elegant.

    BTW, I did try defining an implicit Int64 operator, it didn't help:

    public static implicit operator Int64(ClientID c) { return c.Value; }

    Can anyone help me in coercing LINQ to SQL to generate a comparison just as if my ClientID class were an Int64?

    Thanks,

    Don

    • Edited by drdwilcox Monday, October 31, 2011 6:45 PM
    Monday, October 31, 2011 4:52 PM

Answers

  • Yes Alan. As I said in my question, I know I can do that. In fact, all 157 occurances of the expression in my project use variations of that form.  What I was wondering if there is a way to coerce LINQ To SQL to generate SQL without my having to explicitly reference the .Value property. Since I am pretty sure from the source that there are no attributes that I can use to direct the process, I was wondering if there was a way to nudge the expression tree so that behind the scenes the .Value property was referenced in the expression tree whenever I wrote:

    where i.ClientID == searchID
    

    I've decided there isn't, but I'd love to be proven wrong. Unfortunately, I don't have time to experiment.

    Thursday, November 3, 2011 2:59 PM

All replies

  • Hi drdwilcox,

    Welcome!

    According to your description, the LINQ to SQL provider couldn't recognise the comparison operator which is defined by yourself.

    If you watch the T-SQL by SQL Profiler, you will find "==" in client will be tranlate to "=" in database.

    The workaround is: you can add .ToList() method to load all the records of the type in to memory, then your query should work.

     ClientID searchID = something;
        from i in someSQLdatasource.ToList()
        where i.ClientID == searchID
        select i;
    
    

    In you way, just want to use Constructor to set the this value, actually, C# is a type-safe language.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, November 1, 2011 6:02 AM
    Moderator
  • Alan,

    Thank you for your answer, but that is not an option. It would result in bringing back from the database all the records across ClientIDs, and doing the filtering on the web server. I would like an option that produces SQL from my LINQ.

    The problem is that LINQ to SQL is not able to process the expression tree generated by my where clause. Looking at the code for System.LINQ.Data using ILSpy, the problem is in SqlFormatter.VisitValue, which doesn't even try to generate code for nodes that have the isClientSpecified flag set. I haven't looked, but I assume that the ClientID object has that flag specified, so LINQ to SQL bails.

    What would be needed is a way to coerce the C# compiler into generating a slightly difference expression tree, one that had the actual long Value property rather than the ClientID object at the node.

    Tuesday, November 1, 2011 4:14 PM
  • Hi drdwilcox,

    Thanks for your feedback.

    I think you there is no need to write the comparision method(==) by youself.

      class ClientID {
            public ClientID(long i) { Value = i; }
           // ... other constructors  
            public long Value { get; private set; }
          //  public static bool operator ==(ClientID lhs, long rhs) { return lhs.Value == rhs; }
        }
    
      ClientID searchID = something;
       
        from i in someSQLdatasource
        where i.ClientID.Value == searchID
        select i;//get is public
    
    

    Have a nice day. 

     


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, November 3, 2011 7:44 AM
    Moderator
  • Yes Alan. As I said in my question, I know I can do that. In fact, all 157 occurances of the expression in my project use variations of that form.  What I was wondering if there is a way to coerce LINQ To SQL to generate SQL without my having to explicitly reference the .Value property. Since I am pretty sure from the source that there are no attributes that I can use to direct the process, I was wondering if there was a way to nudge the expression tree so that behind the scenes the .Value property was referenced in the expression tree whenever I wrote:

    where i.ClientID == searchID
    

    I've decided there isn't, but I'd love to be proven wrong. Unfortunately, I don't have time to experiment.

    Thursday, November 3, 2011 2:59 PM