Why does the query generated is different?

Question

I have this:

    Dim compareAddress1 = (From d In db.Addresses Where d.Address1.Equals("a") And _
               d.Address2.Equals(Nothing) And _
               d.City.Equals(Nothing) And _
              d.POBox.Equals(Nothing) And _
              d.PostalCode.Equals(Nothing) And _
              d.ZipCode.Equals(Nothing))

    Dim compareAddress2 = (From d In db.Addresses Where _
                    d.Address1.Equals(_address.Address1) And _
                    d.Address2.Equals(_address.Address2) And _
                    d.City.Equals(_address.City) And _
                    d.POBox.Equals(_address.POBox) And _
                    d.PostalCode.Equals(_address.PostalCode) And _
                    d.ZipCode.Equals(_address.ZipCode))

all field in the object _address are nullable.

When I use the object, I get this query:

    SELECT [t0].[AddressID], [t0].[Address1], [t0].[Address2], [t0].[City], [t0].[CountryProvinceID], [t0].[POBox], [t0].[PostalCode], [t0].[ZipCode]
    FROM [dbo].[Address] AS [t0]
    WHERE ([t0].[Address1] = @p0) AND ([t0].[Address2] = @p1) AND ([t0].[City] = @p2) AND ([t0].[POBox] = @p3) AND ([t0].[PostalCode] = @p4) AND ([t0].[ZipCode] = @p5)
    -------------------------------
    @p0 [AnsiString]: a
    @p1 [AnsiString]: 
    @p2 [AnsiString]: 
    @p3 [AnsiString]: 
    @p4 [AnsiString]: 
    @p5 [AnsiString]:

when I hardcode the data, I got this query:

    SELECT [t0].[AddressID], [t0].[Address1], [t0].[Address2], [t0].[City], [t0].[CountryProvinceID], [t0].[POBox], [t0].[PostalCode], [t0].[ZipCode]
    FROM [dbo].[Address] AS [t0]
    WHERE ([t0].[Address1] = @p0) AND ([t0].[Address2] IS NULL) AND ([t0].[City] IS NULL) AND ([t0].[POBox] IS NULL) AND ([t0].[PostalCode] IS NULL) AND ([t0].[ZipCode] IS NULL)
    -------------------------------
    @p0 [AnsiString]: a

anyone know why? and how to I fix my issue?

Answer 1

Scott Guthrie created an add on, that works in the debugger.

get it here

Answer 2

How about LINQ to SQL Debug Visualizer:

http://weblogs.asp.net/scottgu/archive/2007/07/31/linq-to-sql-debug-visualizer.aspx

Answer 3

Give this a shot...

Dim results = db.Addresses.Where(Function(d) d.Address1.Equals(_address.Address1) And _
                                             d.Address2.Equals(_address.Address2) And _
                                             d.City.Equals(_address.City) And _
                                             d.POBox.Equals(_address.POBox) And _
                                             d.PostalCode.Equals(_address.PostalCode) And _
                                             d.ZipCode.Equals(_address.ZipCode))

Console.WriteLine(db.GetCommand(results).CommandText)

Dim compareAddresses = results.SingleOrDefault()

Answer 4

Are you running this linq against sql sever 2002 i have seen some strange behavior (same as yours) when running linq against sql 2000