How to query database using Entity to SQL

ADSCNET

Dear All, I wrote my db classes then created a web form and added GridView to list all the raws from the Employee table which works fine using the below method/code: WebForm page code:

GridView runat="server" ID="vgEmployees" PageSize="5" Width="100%" ItemType="TestApp.Employee"
DataKeyNames="ID" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False"
SelectMethod="GetEmployees"

WebForm Code behind:

Private ReadOnly _db As New TestAppDbContext

Public Function GetEmployees() As IQueryable(Of Employee)
Return _db.DbSet_Employees
End Function

All above work perfectly, now added a search form where it takes input from the user they query the db and return only the results & display it same gridview, (say): Enter data to search for: Employee Name: ............ Employee Dept: ...... [Search] The Question: How to query the database using Entity to SQL? and is E2S good enough or should move to L2E? Thanks for you help.

ADSCNET

I found it, this should do the work

Public Function GetEmployees() As IQueryable(Of Employee)
sSql = "Select * from Employee where Name1 like '%" + Me.txtName.Text & "%'" & _
" and family like '%" & Me.txtFamily.Text & "%'"

    Return \_db.DbSet\_Employees.SqlQuery(sSql).AsQueryable

End Function

Richard Deeming

ADSCNET wrote:

"Select * from Employee where Name1 like '%" + Me.txtName.Text & "%'" & _ " and family like '%" & Me.txtFamily.Text & "%'"

Try typing the following into txtFamily:

Robert';DROP TABLE Employee;--

Your query then becomes:

Select * from Employee where Name1 like '%%' and family like '%Robert';DROP TABLE Employee;--%'

That's two queries - one to select from the Employee table, and one to drop the Employee table. The -- comments out the rest of the query. Congratulations - you've just discovered SQL Injection[^] and met little Bobby Tables[^]. Try something like this instead:

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

That will generate a parameterized query which will not be susceptible to SQL injection.

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

ADSCNET

Hi Richard, Many thanks to you.. this is the type of reply & info I was waiting for, excellent info.

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 :)