How to query database using Entity to SQL

[ADSCNET]

Hi Richard, Any idea how to use COALESCE function to avoid the Null effect on the results, I can use the function with the sql query but the way you suggested as parameterized query not working with me .. sure I am missing somthing. Any suggestions? Thanks.

[Richard Deeming]

What's the query you're trying to execute?

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[ADSCNET]

It's the same query I used in my previous example, just added to it a telephone field where in the database most of the case is null.

[Richard Deeming]

So you're looking for something like this?

Return From employee In _db.DbSet_Employees _
Where employee.Name1.Contains(Me.txtName.Text) _
AndAlso employee.Family.Contains(Me.txtFamily.Text)
AndAlso (employee.Telephone Is Nothing OrElse employee.Telephone.Contains(Me.txtTelephone.Text))

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[ADSCNET]

Excellent ... I didn't thought to solve it this way .. funny :) I was insisting to use the Coalesce. Thanks Richard.

[ADSCNET]

After some testing, keeping the field default Null values is not practical, coz if in tele field entered number doesn't exist still it returns the record, say the field is Null and in txtMobile entered 7777888 it still returns the raw as found. But if replaced the Null with "" value it works fine, therefore I am wondering if there is a way around it or if it's (advisable or not) to not keep any Null as default value in all the db fields & replace it with "".

[Richard Deeming]

So something like:

If String.IsNullOrWhiteSpace(Me.TxtTelephone.Text) Then
Return From employee In _db.DbSet_Employees _
Where employee.Name1.Contains(Me.txtName.Text) _
AndAlso employee.Family.Contains(Me.txtFamily.Text)
End If

Return From employee In _db.DbSet_Employees _
Where employee.Name1.Contains(Me.txtName.Text) _
AndAlso employee.Family.Contains(Me.txtFamily.Text)
AndAlso employee.Telephone IsNot Nothing
AndAlso employee.Telephone.Contains(Me.txtTelephone.Text)

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[ADSCNET]

I don't think this practical, what if FamilyName field is Null in the db ... correct? what do you think about what I said in my last post about replacing the Null.

[Richard Deeming]

You shouldn't need to replace the null values. Try something like this:

Dim query As IQueryable(Of Employee) = _db.DbSet_Employees

If Not String.IsNullOrWhiteSpace(Me.txtName.Text) Then
query = From employee In query Where employee.Name1.Contains(Me.txtName.Text)
End If

If Not String.IsNullOrWhiteSpace(Me.txtFamily.Text) Then
query = From employee In query Where employee.Family.Contains(Me.txtFamily.Text)
End If

If Not String.IsNullOrWhiteSpace(Me.txtTelephone.Text) Then
query = From employee In query Where employee.Telephone.Contains(Me.txtTelephone.Text)
End If

Return query

Applying multiple Where clauses is the same as using AndAlso within a single clause. The query is translated to SQL, so if you're searching on a field which is null, the record will be ignored; you won't get an exception like you would if you were executing the code in VB.

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[ADSCNET]

Richard thank you sooooo much for the time you are spending to answer my issues .. Now, regarding your code what I don't understand why you are putting if condition to check if the text box is empty or not? my problem is not with if the text is empty or not my problem is with the field in the database if the field is Null its a problem .. to follow your code, if the txtbox is not empty (which has some data to pass) and the field in the db is Null again it will return same false result.

[Richard Deeming]

Taking the telephone as an example:

If Not String.IsNullOrWhiteSpace(Me.txtTelephone.Text) Then
query = From employee In query Where employee.Telephone.Contains(Me.txtTelephone.Text)
End If

• If you've not entered anything the txtTelephone box, you don't want to filter by telephone number, so we don't include that filter.
• If you have entered something in the txtTelephone box, you only want to return records where the Telephone column is not null, and contains the text that you've entered.

If this code was executing in VB, and the Telephone property returned Nothing, you'd get a NullReferenceException when you tried to call the Contains method on it. However, since you're using LINQ, the filter will be translated to SQL:

... And Telephone Like '%123456%'

In SQL, this will not generate an error if the Telephone column is null; it will just exclude that row from the results.

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[ADSCNET]

Great it works .. I just started with EF I used to use before for data access layer ado.net So this is LINQ to Entity OR Entity to SQL? as far as I know these are the only 2 data access methods to use in EF.

[Richard Deeming]

This is LINQ to Entities. Entity SQL[^] is a totally different beast, which you'll probably never need to use.

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[ADSCNET]

Thanks a lot :)