Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

LINQ to Entities

Introduction:

I have previously written articles on LINQ to SQL, LINQ to XML, LINQ to Objects, and LINQ to Dataset, as I said previously I will try to write articles on LINQ all features.

According to me LINQ made lot of things easy for all .net developers, big thanks to Microsoft team for providing such a great framework.

In this article I will describe LINQ to Entities in detail with some sample examples.

What is LINQ-to-Entities?

LINQ-to-Entities operates on entity framework entities to access the data from the underlying database. You can use LINQ method syntax or query syntax when querying with EDM.

Syntax:

LINQ to Entities queries can be composed in two different syntaxes:

1.      Query expression syntax

Query expression syntax contains set of clauses written in a declarative syntax like Transact-SQL or XQuery, this is new in C# 3.0 and VB 9.0.
CLR can’t read query syntax itself, therefore at compile time, query expressions are translated to something that the CLR does understand: method calls.

Example:

Flowing example linq to entities query expression based query returns only valid comments.

     using (var productDbContext = new ApplicationServices())
            {
                var ValidComments = (from c in productDbContext.ProductComment
                                  where c.ProductId!=0&&c.Title!=string.Empty
                                   select new
                                   {
                                       Id = c.Id,
                                       Title = c.Title,
                                       Description = c.Decription,
                                       CutomerId = c.CutomerId,
                                       ProductId = c.ProductId
                                   }
 
                     ).ToList();
            }

 

2.      Method-based query syntax.

The method-based query syntax is a sequence of direct method calls to LINQ operator methods, passing lambda expressions as the parameters.

Example:

Flowing example linq to entities method based query returns only valid comments.

      using (var productDbContext = new ApplicationServices())
            {
        var ValidComments = productDbContext.ProductComment
       .Where(comment => comment.ProductId != 0&&comment.Title!=string.Empty)
       .Select(c => new { c.Id, c.Title, c.Decription, c.ProductId });
            }
 

How to create and execute LINQ to Entities?

1. Construct an ObjectQuery instance from ObjectContext.

2. Compose a LINQ to Entities query in C# or Visual Basic by using the ObjectQuery instance.

3. Convert LINQ standard query operators and expressions to command trees.

4. Execute the query, in command tree representation, against the data source. Any exceptions thrown on the data source during execution are passed directly up to the client.

5. Return query results back to the client.

Why LINQ-to-Entities?

LINQ to Entity is part of ADO.NET entity framework reason this is more flexible.
LINQ to SQL has limitations with other data providers like Oracle, MySQL, these limitations are not Using LINQ to entities.
You can perform all sql query like logics with LINQ to Entities using minimum code, these some advantages makes linq to entities best choice to programmer.

Advantages using LINQ-to-Entities:

1. Easy to use and readable compared with old complex approach for each loop.

2. Performance is good compared with old complex for each loop/multiple line code.

3. You can perform sorting, grouping on xml data.

4. Compared with old complex approach one of the benefits of LINQ to Entities is that it's only one statement, finally reduced code easy to manage.

5. LINQ to Entities will generate all the appropriate SQL for you.

6. LINQ to Entities take care of generating all the ADO.NET activity, and manage opening and closing the connections.

7. Extension method feature enables you to, in effect, "add" new methods to existing types without actually modifying them.

8. You can Join Multiple entities data data into One Output Sequence, You can apply joins on entities like sql table joins.

9. Following are some common advantages applicable for all LINQ features:

a. Standardized way to query multiple data sources
b. Compile time safety of queries
c. Optimized way to perform set based operations on in memory objects
d. Ability to debug queries

LINQ to Entities inner join Examples:

Following LINQ to Entities query expression syntax based query return product details with category data, in query we inner query on product and categories entities and retrieved only matching data.

Example:

    using (var productDbContext = new ApplicationServices())
     {
      var productDetails = (from p in productDbContext.Product
                       join c in productDbContext.Category
                       on new { PID = p.CategoryID } equals new { PID = c.CategoryID }
                       select new
                           {
                                      ProductID = p.ProductID,
                                      ProductName = p.ProductName,
                                      Description = p.Description,
                                      ImagePath = p.ImagePath,
                                      UnitPrice = p.UnitPrice,
                                      CategoryID = p.CategoryID,
                                      Category = c.CategoryName,
                            }
 
                     ).ToList();
               
            }


Conclusion:

Avoid using old foreach loop approach instead try LINQ to entities, this will reduce your code, make it simple and readable. I suggest if you want to retrieve data from multiple tables prefer LINQ to Entities over LINQ to SQL.

Reference used:

https://msdn.microsoft.com/en-us/library/bb386964(v=vs.100).aspx

https://msdn.microsoft.com/en-us/library/bb399367(v=vs.100).aspx


धन्यवाद मित्रानो ...