Querying with EDM



Entity framework supports three types of queries: 1) LINQ to Entities 2) Entity SQL 3) Native SQL

1) LINQ to Entities: Language-Integrated Query (LINQ) is a powerful query language introduced in Visual Studio 2008. You can use LINQ in C# or Visual Basic to query different data sources. LINQ-to-Entities operats on entity framework entities to access the data from the underlaying database. You can use LINQ method syntax or query syntax to querying with EDM. Visit LINQ Tutorials to learn LINQ step-by-step.

LINQ Method syntax:

//Querying with LINQ to Entities 
    using (var context = new SchoolDBEntities())
    {
        var L2EQuery = context.Students.where(s => s.StudentName == "Bill");
        
        var student = L2EQuery.FirstOrDefault<Student>();

    }

LINQ Query syntax:

    using (var context = new SchoolDBEntities())
    {
        var L2EQuery = from st in context.Students
                       where st.StudentName == "Bill"
                       select st;
   
        var student = L2EQuery.FirstOrDefault<Student>();
     }

2) Entity SQL: Entity SQL is another way to create a query. It is processed by the Entity Framework’s Object Services directly. It returns ObjectQuery instead of IQueryable.

You need ObjectContext to create a query using Entity SQL.

The following code snippet shows the same query result as L2E query above.

 //Querying with Object Services and Entity SQL
    string sqlString = "SELECT VALUE st FROM SchoolDBEntities.Students " +
                        "AS st WHERE st.StudentName == 'Bill'";
    
    var objctx = (ctx as IObjectContextAdapter).ObjectContext;
                
    ObjectQuery<Student> student = objctx.CreateQuery<Student>(sqlString);
                    Student newStudent = student.First<Student>();
    

You can also use EntityConnection and EntityCommand to execute Entity SQL as shown below:

 
    using (var con = new EntityConnection("name=SchoolDBEntities"))
    {
        con.Open();
        EntityCommand cmd = con.CreateCommand();
        cmd.CommandText = "SELECT VALUE st FROM SchoolDBEntities.Students as st where st.StudentName='Bill'";
        Dictionary<int, string> dict = new Dictionary<int, string>();
        using (EntityDataReader rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.CloseConnection))
        {
                while (rdr.Read())
                {
                    int a = rdr.GetInt32(0);
                    var b = rdr.GetString(1);
                    dict.Add(a, b);
                }
        }               
    }

3) Native SQL You can execute native SQL queries for a relational database as shown below:

 using (var ctx = new SchoolDBEntities())
    {
        var studentName = ctx.Students.SqlQuery("Select studentid, studentname, standardId from Student where studentname='Bill'").FirstOrDefault<Student>();
    }    

Learn to execute raw sql query using DbContext in Raw SQL Query section.