How to Write Inline SQL-Queries in DataContext Class
For a new programmer in Linq writing Complex Linq queries like Joins for multiple tables , using Lambda expressions and preparing dynamic queries is a bit difficult thing.Because he/she is not Used to it.For all those Programmers this article will give some relief and they can write ordinary queries and execute in datacontext to get Object as resultset.This Article will demonstrate how to use these Methods. I will illustrate with snippet of Code Lines.
Today everybody using Entity Framework. Many People are bit worried writing complex queries using Joins,group by and all other type of conditions in Linq type of queries.But there is a Method you can eliminate/avoid to write Complex queries and lambda expressions in Linq and you can use simple inline queries and return as a Object as resultset same as Linq query resultset.That Method is none other than ExecuteQuery() and you can directly execute Insert/Update/Delete Operations with ExecuteCommand().
This(ExecuteQuery()) Method has two overload list.
Note:whereever i written less than or greater than please assume them they are mathematical symbols replace less than and greater than and write mathematical symbols
Executes SQL queries directly on the database and returns objects.
ExecuteQuery < tresult >(String, Object[])
Executes SQL queries directly on the database.
ExecuteQuery(Type, String, Object[])
Datacontext db=new DataContext(constring)
var customers = db.ExecuteQuery (lessthan) Customer (greaterthan)(@"SELECT CustomerID, CompanyName, ContactName, ContactTitle,
Address, City, Region, PostalCode, Country, Phone, Fax
FROM dbo.Customers
WHERE City = {0}", "London");
foreach (Customer c in customers)
Console.WriteLine(c.ContactName);
In Visual Basic
Dim customers = db.ExecuteQuery(Of Customer)("SELECT CustomerID, _ CompanyName, ContactName, ContactTitle, _
Address, City, Region, PostalCode, Country, Phone, Fax _
FROM dbo.Customers _
WHERE City = {0}", "London")
For Each c As Customer In customers
Console.WriteLine(c.ContactName)
Next
For Joins Syntax
IEnumerable lessthan Member greater than results =db.ExecuteQuery lessthan
Member greaterthan(@"SELECT *
FROM Member
INNER JOIN aspnet_Users ON Member.user_id = aspnet_Users.UserId
WHERE [aspnet_Users].[UserName] = {0}", "Marina2");
You can write the Update/Insert/Delete command with this.
db.ExecuteCommand("UPDATE Products SET UnitPrice = UnitPrice + 1.00");
Points of interest :
These Methods will work on .NET Framework 4.5,4,3.5
Follow