none
Where clause containing non-dataset criteria RRS feed

  • Question

  • I have been asked the following because of a code-review of my select statement.

    What is the effect to LINQ of including criteria in the where clause that are external to the dataset? How does this affect the SQL generated by LINQ? Does this mean that additional redundant data may be returned, or is it clever enough to construct SQL that won’t do this? Is LINQ intended to allow this, or is this a case of something that can be done but shouldn’t be? Should separate queries be constructed instead?

    For example; in the following code sample, the value of whichAddress is nothing todo with the data set, but affects the query.

    public void fn (johnsEnum whichAddress, string sourceAddress)
    {
    var res = (from pp in xyz.something
               where ((whichAddress == johnsEnum.email) && (pp.email == sourceAddress))
               ||    ((whichAddress == johnsEnum.twitter) && (pp.twitter == sourceAddress))
               select pp).SingleOrDefault();
    }
    Thanks,
    John.

    Monday, February 8, 2010 4:31 PM

Answers

  • Everything in your statement that is "external" are constants, and they will be translated into SQL as such.  No redundant data will be returned.  Simple comparisons, conversions, etc. are easily translatable into SQL and I don't believe it is much strain on SQL Server to deal with it.  If the local logic gets a little more complex, then you may need to do some of the processing locally.  For example, to demonstrate this for your case you could decide on which where clause to use locally, such as:

            public void fn(johnsEnum whichAddress, string sourceAddress)
            {
                var q0 = xyz.something.AsQueryable();
    
                if (whichAddress == johnsEnum.email)
                    q0 = q0.Where(pp => pp.email == sourceAddress);
                else if (whichAddress == johnsEnum.twitter)
                    q0 = q0.Where(pp => pp.twitter == sourceAddress);
                else
                    q0 = q0.Where(pp => false);
    
                var res = q0.SingleOrDefault();
    
                // ...
            }
    

    If your logic is too complex, or you had the need to call a local method from inside your where(), then you will not be able to translate into SQL.  In this case you may need to convert to an IEnumerable (say by calling ToList()) and bringing it into memory.  This would then get redundant data and you would have to proceed with caution.
    Monday, February 8, 2010 7:27 PM
  • You get that error because the compiler decided that q0 was of type IQueryable<xyz.something>.  You just need a new variable, such as:

         var q1 = q0.Select(pp => pp.personId);

    This will then be of type IQueryable<int>.
    • Marked as answer by John T. Angle Tuesday, April 27, 2010 2:15 PM
    Monday, February 15, 2010 6:57 PM

All replies

  • Everything in your statement that is "external" are constants, and they will be translated into SQL as such.  No redundant data will be returned.  Simple comparisons, conversions, etc. are easily translatable into SQL and I don't believe it is much strain on SQL Server to deal with it.  If the local logic gets a little more complex, then you may need to do some of the processing locally.  For example, to demonstrate this for your case you could decide on which where clause to use locally, such as:

            public void fn(johnsEnum whichAddress, string sourceAddress)
            {
                var q0 = xyz.something.AsQueryable();
    
                if (whichAddress == johnsEnum.email)
                    q0 = q0.Where(pp => pp.email == sourceAddress);
                else if (whichAddress == johnsEnum.twitter)
                    q0 = q0.Where(pp => pp.twitter == sourceAddress);
                else
                    q0 = q0.Where(pp => false);
    
                var res = q0.SingleOrDefault();
    
                // ...
            }
    

    If your logic is too complex, or you had the need to call a local method from inside your where(), then you will not be able to translate into SQL.  In this case you may need to convert to an IEnumerable (say by calling ToList()) and bringing it into memory.  This would then get redundant data and you would have to proceed with caution.
    Monday, February 8, 2010 7:27 PM
  • Ooh, that's interesting! Thanks Fermat8.

    On a bit of a tangent, how would I change your code so that I only returned a specific column rather than the whole row? I've tried it with

    q0 = q0.Select(pp => pp.personId)

    but it complained that it cannot implicitly convert the System.Linq.IQueryable<long?> to 'System.Linq.IQueryable<xyz.something>'.

    Tuesday, February 9, 2010 10:20 AM
  • You get that error because the compiler decided that q0 was of type IQueryable<xyz.something>.  You just need a new variable, such as:

         var q1 = q0.Select(pp => pp.personId);

    This will then be of type IQueryable<int>.
    • Marked as answer by John T. Angle Tuesday, April 27, 2010 2:15 PM
    Monday, February 15, 2010 6:57 PM