is there a better way to deal with varchar field that accept null?

Question

I got that "ugly" linq to sql query:

Dim f = (From d In db.Addresses Where ((_address.Address1 Is Nothing AndAlso d.Address1.Equals(Nothing)) OrElse (_address.Address1 IsNot Nothing AndAlso d.Address1.Equals(_address.Address1))) And _
                                      ((_address.Address2 Is Nothing AndAlso d.Address2.Equals(Nothing)) OrElse (_address.Address2 IsNot Nothing AndAlso d.Address2.Equals(_address.Address2))) And _
                                      ((_address.City Is Nothing AndAlso d.City.Equals(Nothing)) OrElse (_address.City IsNot Nothing AndAlso d.City.Equals(_address.City))) And _
                                      ((_address.POBox Is Nothing AndAlso d.POBox.Equals(Nothing)) OrElse (_address.POBox IsNot Nothing AndAlso d.POBox.Equals(_address.POBox))) And _
                                      ((_address.PostalCode Is Nothing AndAlso d.PostalCode.Equals(Nothing)) OrElse (_address.PostalCode IsNot Nothing AndAlso d.PostalCode.Equals(_address.PostalCode))) And _
                                      ((_address.ZipCode Is Nothing AndAlso d.ZipCode.Equals(Nothing)) OrElse (_address.ZipCode IsNot Nothing AndAlso d.ZipCode.Equals(_address.ZipCode))) And _
                                      ((_address.CountryProvinceID Is Nothing AndAlso d.CountryProvinceID.Equals(Nothing)) OrElse (_address.CountryProvinceID IsNot Nothing AndAlso d.CountryProvinceID.Equals(_address.CountryProvinceID))))

so it generate 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] IS NULL) AND ([t0].[Address2] = @p0) AND ([t0].[City] IS NULL) AND ([t0].[POBox] IS NULL) AND ([t0].[PostalCode] = @p1) AND ([t0].[ZipCode] IS NULL) AND ([t0].[CountryProvinceID] IS NULL)
-------------------------------
@p0 [AnsiString]: a
@p1 [AnsiString]: a

is there a way to clean it, just a little?

what it does is checking in the table if an address match the one typed by the user

Answer 1

What you've posted doesn't look to me like it's checking for an empty string, unless you're referring to PostalCode and @p0 being the empty string.

If that's the case I would do this:

(From d In db.Addresses Where (d.PostalCode.Equals(Nothing) 
    OrElse (d.PostalCode.Equals(_address.PostalCode) 
        AndAlso Not String.IsNullOrEmpty(d.PostalCode))))

Answer 2

haaa.... the solution is.....

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

damn it, lost a few hours for THAT???