How to query database using Entity to SQL
-
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 FunctionAll 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.
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).AsQueryableEnd Function
-
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).AsQueryableEnd Function
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
Employeetable, and one to drop theEmployeetable. 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 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
Employeetable, and one to drop theEmployeetable. 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 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
Employeetable, and one to drop theEmployeetable. 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
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.
-
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.
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
-
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
-
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.
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
-
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
-
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
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 "".
-
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 "".
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 IfReturn 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
-
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 IfReturn 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
-
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.
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 IfIf Not String.IsNullOrWhiteSpace(Me.txtFamily.Text) Then
query = From employee In query Where employee.Family.Contains(Me.txtFamily.Text)
End IfIf Not String.IsNullOrWhiteSpace(Me.txtTelephone.Text) Then
query = From employee In query Where employee.Telephone.Contains(Me.txtTelephone.Text)
End IfReturn query
Applying multiple
Whereclauses is the same as usingAndAlsowithin 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
-
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 IfIf Not String.IsNullOrWhiteSpace(Me.txtFamily.Text) Then
query = From employee In query Where employee.Family.Contains(Me.txtFamily.Text)
End IfIf Not String.IsNullOrWhiteSpace(Me.txtTelephone.Text) Then
query = From employee In query Where employee.Telephone.Contains(Me.txtTelephone.Text)
End IfReturn query
Applying multiple
Whereclauses is the same as usingAndAlsowithin 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
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 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.
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
txtTelephonebox, you don't want to filter by telephone number, so we don't include that filter. - If you have entered something in the
txtTelephonebox, you only want to return records where theTelephonecolumn is not null, and contains the text that you've entered.
If this code was executing in VB, and the
Telephoneproperty returnedNothing, you'd get aNullReferenceExceptionwhen you tried to call theContainsmethod 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
Telephonecolumn 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
- If you've not entered anything the
-
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
txtTelephonebox, you don't want to filter by telephone number, so we don't include that filter. - If you have entered something in the
txtTelephonebox, you only want to return records where theTelephonecolumn is not null, and contains the text that you've entered.
If this code was executing in VB, and the
Telephoneproperty returnedNothing, you'd get aNullReferenceExceptionwhen you tried to call theContainsmethod 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
Telephonecolumn 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
- If you've not entered anything the
-
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.
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
-
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
