Category: LINQ

LinqToSql Count

You can pass the results of a linq query to the next statement like this one

var q = context.Products
                .Where(p => p.ProductName.StartsWith(“A”))
                .Count();
and that should provide the count of products with names starting with “A”. 
Further it would make no difference (performance wise) to skip theWhere statement and simply call the Count with filter as parameter, like:
var q = context.Products
                .Count(p => p.ProductName.StartsWith(“B”));
or in another syntax like:
    q = (from p in context.Products
         where p.ProductName.StartsWith(“C”)
         select true).Count();
So, I did put a tracing into the Context.Log to see what it goes to the SQL-Server. I’ve got the following for all three cases:
SELECT COUNT(*) AS [value]
FROM [dbo].[Products] AS [t0]
WHERE [t0].[ProductName] LIKE @p0
— @p0: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [A%]
— Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1

Linq: Group by and sort

The following LINQ statement was the first one I wrote:


var hsQ = from hspt in hospitals
          orderby hspt.County, hspt.City
          group hspt by hspt.County;

It obviously groups by country and then sorts by city.

However, I found the following even better as it groups first by country, sorts by country and then sorts within the group by city:


var hsQ = from hspt in hospitals
          orderby hspt.City
          group hspt by hspt.County into hsptGroup
          orderby hsptGroup.First().County
          select hsptGroup;

Linq-To-SQL and WCF

WCF works on the objects like disconnected records. The object that is passed to the client is not the same as the one the is about to update. Therefore Linq does not know about the changes made to the record. As a result Linq will not do anything when calling the SubmitChanges.

To sort out this problem we need attach the instance record again to the list in the context:

context.GetTable().Attach(instance, true);

just before we call the SubmitChanges. But this is not always possible. I got the following error when calling this:

An entity can only be attached as modified without original state if it declares a version member or does not have an update check policy.

The simple way of sorting this out was giving the ID field of the record the attribute of

IsVersion=true

This way Linq allows me to attach the existing instance and generate an UPDATE sql-statement.

See Also: http://www.west-wind.com/Weblog/posts/135659.aspx

Read CSV file into LINQ

This article is based on the great book of LINQ in Action. I am learning so much from it and I would like to keep a note of some handy subject from the book while not disturbing any copyright of ther authors. For the full story please buy the book from http://www.manning.com/LINQinAction. 

 
using (StreamReader reader = new StreamReader("books.csv"))
{
  var books = 
    from line in reader.Lines()
    where !line.StartsWith("#")
    let parts = line.Split(',')
    select new {
        Title = parts[1],
        Publisher = parts[3],
        Isbn = parts[0]
    };

  // use the books here ...
}

Join And Group Tables

This article is based on the great book of LINQ in Action. I am learning so much from it and I would like to keep a note of some handy subject from the book while not disturbing any copyright of ther authors. For the full story please buy the book from http://www.manning.com/LINQinAction

Group Join


from publisher in SampleData.Publishers
join book in SampleData.Books
  on publisher equals book.Publisher into publisherBooks
select new { Publisher=publisher.Name, Books=publisherBooks };

This is a group join. It bundles each publisher’s book as sequences named publisherBooks. This new query is equivalent to this one: 


from book in SampleData.Books 
group book by book.Publisher into publisherBooks
select new { Publisher=publisherBooks.Key.Name, Books=publisherBooks };

Inner Join
An inner join essentially finds the intersection between two sequences. With an inner join, the elements from two sequences that meet a matching condition are combined to from a single sequence.


from publisher in SampleData.Publishers
join book in SampleData.Books
  on publisher equals book.Publisher
select new { Publisher=publisher.Name, Book=book.Title };

Left Outer Join
When we want to keep all the elements from the outer sequence, independently of whether there is a matching element in the inner sequence, we need to perform a left outer join.

A left outer join is like an inner inner join, except that all the left-side elements get included at least once, even if they don’t match any right side elements. 


from publisher in SampleData.Publishers
join book in SampleData.Books
  on publisher equals book.Publisher into publisherBooks
from book in publisherBooks.DefaultIfEmpty()
select new { 
  Publisher = publisher.Name, 
  Book = book == default(Book) ? "(no books)" : book.Title
};

DefaultEmpty operator supplies a default element for an empty sequence.
DefaultEmpty uses the default keyword of generics. It returns null for reference types and zero for numeric value types. For structs, it returns each member of the struct initialized to zero or null depending on whether they are value or reference types.

Cross Join
A cross join computes the Cartesian product of all the elements from two sequences. The result is a sequence that contains a combination of each element from the first sequence with eacht element from the second sequence.


from publisher in SampleData.Publishers
from book in SampleData.Books
select new { 
  Correct = (publisher == book.Publisher)
  Publisher = publisher.Name, 
  Book = book.Title
};

Sample Linq Entities

This snippet code shows how easy it is to use LINQ to get to your data in SQL-Server. Before you start you need to add a new ADO.NET Entity Data Model to your project where the wizard leads you to select all you need from your project. You can have multiple modles like OrderModle, PurchaseModle and DistributionModle that may overlap some tables but would make development much easier.
In this sample I use an entity modle from my FamilyTree database.


        AhouraEntities model = new AhouraEntities();
        ObjectQuery people = model.Person;

        var panahy = from person in people
                     where person.Surname.StartsWith("Panah")
                     select new
                     {
                         person.ID,
                         person.Name
                     };

        grdPeople.DataSource = people;
        grdPeople.DataBind();

        lstPanahy.DataSource = panahy;
        lstPanahy.DataBind();

Obviously you could instanciate the model using the constructor that expects a connectionString.

Get XML String from XElement

You can use the WriteTo method of the XElement object to an XmlWriter and this can be created in ten different ways like by giving a filename or passing a stream as output or simply giving the StringBuilder to write to:


/// <summary>
/// Generates XML string from an XElement
/// summary>
/// <param name="xml">XElement source</param>
public string GetXmlString(XElement xml)
{
    // could also be any other stream
    StringBuilder sb = new StringBuilder();
   
    // Initialize a new writer settings
    XmlWriterSettings xws = new XmlWriterSettings();
    xws.OmitXmlDeclaration = true;
    xws.Indent = true;

    using (XmlWriter xw = XmlWriter.Create(sb, xws))
    {               
        // the actual writing takes place
        xml.WriteTo(xw);
    }

    return sb.ToString();

}

It is important to know that the XElement already supports ToString which generates the same results as this code, but when you want to get something else, this could be a good start.


See further information in MSDN.

LINQ Delayed excecution

Link statements generate the query and make it ready to be used when it comes to the point where the data is needed. In the following example when the datasource is set the data is needed and that is when the communication with the database takes place:



var products = 
    from p in db.Products
    orderby p.ProductName
    select new { p.ProductID, p.ProductName, p.UnitPrice };
products = products.Skip(itemsToSkip).Take(pageSize);

DataGridView1.DataSource = products;

LINQ over XML

This example is also from th AppDev:


var doc = CreateDocument(); // returns an XDocument
var items = from item in doc.Descendants("Item")
            where (string)item.Parent.Attribute("Name") == "Breads"
            select item;

now transforming the xml to a new format:


XElement transformed = new XElement("items",
            from item in items
            select new XElement("item",
                   new XAttribute("ItemName", (string)item.Element("Name")),
                   new XAttribute("Price", (string)item.Element("Price))));