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


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

Linq to XML

Introduction:

For any application common approach is use database or XML documents to store backend data.

a. LINQ provides LINQ to SQL component to directly manipulate relational data, you can perform linq queries on Data directly.

b. For XML document .net framework provided LINQ to XML interface that enables In-memory XML document modification.

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

What is LINQ to XML?

LINQ to XML is a LINQ-enabled, in-memory XML programming interface that enables you to work with XML from within the .NET Framework programming languages.

LINQ to XML is like the Document Object Model (DOM) in that it brings the XML document into memory. You can query and modify the document, and after you modify it you can save it to a file or serialize it and send it over the Internet. However, LINQ to XML different from DOM.

Using LINQ to XML, you can

a. Load XML from files or streams.
b. Serialize XML to files or streams.
c. Create XML from scratch by using functional construction.
d. Query XML using XPath-like axes.
e. Manipulate the in-memory XML tree by using methods such as Add, Remove, ReplaceWith, and SetValue.
f. Validate XML trees using XSD.
g. Use a combination of these features to transform XML trees from one shape into another.

Reference: https://msdn.microsoft.com

Why LINQ to XML?

LINQ to XML enables you to create and modify XML documents easily by using LINQ query expressions instead of XPath or XQuery.

Compared with classic complex XML parsing approach LINQ to XML expressions are easy to read xml document and modify XML document.

Advantages using LINQ to XML:

1. LINQ to XML makes XML easier by providing a query experience that is similar to SQL.
2.   Allows to use query result as parameters to XElement and XAttribute object constructors enables a powerful approach to creating XML trees.
This approach, called functional construction, enables developers to easily transform XML trees from one shape to another.
3. Main advantage of LINQ to XML is its integration with LINQ, This integration allows you to write queries on the in-memory XML document to retrieve collections of elements and attributes.
   var result = xdoc.Descendants("Types")
                 .Descendants("Animals")
                 .Descendants("Tab")
                 .Elements("Dogs")
                 .Where(n => n.Attribute("id").Value == "1");
4. Easy to create XML trees with LINQ to XML.
5. Compiled and in memory use, so it runs faster.
6. Allows for better string manipulation.
7. Ability to work directly with XML elements instead of creating a container XML document, as required with W3C DOM.
8. In-memory XML document modification that is powerful, yet simpler to use than XPath or XQuery.
9. You can perform sorting, grouping on xml data.

LINQ to XML Example with Sorting Data:

Following example LINQ to xml query sort’s student’s xml data by year in ascending order.

XML:

<?xml version="1.0" encoding="UTF-8"?>
<
Collage>
  <
Students>
    <
Student>Shourya</Student>
    <
Year>2014</Year>
    <
teacher>Nagnath</teacher>
  </
Students>
  <
Students>
    <
Student>Shrad</Student>
    <
Year>2015</Year>
    <
teacher>Girish</teacher>
  </
Students>
  </
Collage>

LINQ to XML Query:

IEnumerable<decimal> Students =
 
         from el in doc.Elements("Students")
 
         let year = (decimal)el.Element("Year")
 
         orderby year
 
         select year;
           foreach (decimal student in Students)
               Console.WriteLine(student);

 

LINQ to XML methods with use of extension method:

In following example count method is used with XML document  

var doc = XDocument.Load("~/nmk/students.xml");
int descendantsNodes =doc.Descendants().Count(); // counts ALL descendants elements
int childNodes = doc.Root.Elements().Count(); //counts direct children of the root element


User Defined Extension methods with LINQ to XML:

In following example I have created extension methods for data casting, those methods I used with LINQ to XML query.

  var products =
               from elem in xElem.Descendants("Product")
               orderby elem.Attribute("ProductName").Value
               select new Product
               {
                   ProductId = elem.Attribute("ProductId").GetAsInteger(),
                   ProductName = elem.Attribute("ProductName").GetAsString(),
                   Category =
                      elem.Attribute("Category").GetAsString(),
                   Price = elem.Attribute("Price").GetAsDecimal()
               };


User Defined Extension method:

public static string GetAsString(this XAttribute attrbute)
  {
    string ret = string.Empty;
 
    if (attrbute != null && !string.IsNullOrEmpty(attrbute.Value))
    {
      ret = attrbute.Value;
    }
 
    return ret;
  }

 

Using existing .net Framework provided extension methods with LINQ to XML:

Following LINQ to XML query example returns valid products data for existing categories only

var ValidProductsDeatils = xElem
 .Element("Products")
 .Elements("Product")
 .Where
 (
     x =>  x .Elements("Category")
     .Where
     (
         z => z.Attribute("Id").Value !="0"
     )
     .Select
     (
         z =>
         z.Attribute("Name").Value
     ).SingleOrDefault()=="true"
 );