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)

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

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

Article by srirama
A Good advice from parent to a Child , Master to a Student , Scholar to an Ignorant is like a doctor prescribed pill it is bitter to take but when they take it will do all good for them --- Bhushan

Follow srirama or read 74 articles authored by srirama


No responses found. Be the first to comment...

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: