LINQ Exchange

Well, it's official – LINQ to SQL is NOT dead in .NET 4.0. I'll say it again – LINQ to SQL is not dead.

Damian G, a developer at Microsoft working on LINQ to SQL for .NET 4.0, recently made a list of changes that will be coming out in the next release of .NET.

Change list

Performance

• Query plans are reused more often by specifically defining text parameter lengths
• Identity cache lookups for primary key with single result now
  includes
  query.Where(predicate).Single/SingleOrDefault/First/FirstOrDefault
• Reduced query execution overhead when DataLoadOptions specified
  (cache lookup considers DataLoadOptions value equivalency – post beta
  1)

Usability

• ITable<T> interface for additional mocking possibilities
• Contains with enums automatically casts to int or string depending on column type
• Associations can now specify non-primary-key columns on the other end of the association for updates
• Support list initialization syntax for queries
• LinqDataSource now supports inherited entities
• LinqDataSource support for Dynamic Data query extenders added

Query stability

• Contains now detects self-referencing IQueryable and doesn't cause a stack overflow
• Skip(0) no longer prevents eager loading
• GetCommand operates within SQL Compact transactions
• Exposing Link<T> on a property/field is detected and reported correctly
• Compiled queries now correctly detect a change in mapping source and throw
• String.StartsWith, EndsWith and Contains now correctly handles ~ in the search string
• Now detects multiple active result sets (MARS) better
• Associations are properly created between entities when using eager loading with Table-Valued Functions (TVFs)
• Queries that contain sub-queries with scalar projections now work better

Update stability

• SubmitChanges no longer silently consumes transaction rollback exceptions
• SubmitChanges deals with timestamps in a change conflict scenario properly
• IsDbGenerated now honors renamed properties that don't match underlying column name
• Server-generated columns and SQL replication/triggers now work instead of throwing SQL exception

General stability

• Binary types equate correctly after deserialization
• EntitySet.ListChanged fired when adding items to an unloaded entity set
• Dispose our connections upon context disposal (ones passed in are untouched)

SQL Metal

• Foreign key property setter now checks all affected associations not just the first
• Improved error handling when primary key type not supported
• Now skips stored procedures containing table-valued parameters instead of aborting process
• Can now be used against connections that use AttachDbFilename syntax
• No longer crashes when unexpected data types are encountered

LINQ to SQL class designer

• Now handles a single anonymously named column in SQL result set
• Improved error message for associations to nullable unique columns
• No longer fails when using clauses are added to the partial user class
• VarChar(1) now correctly maps to string and not char
• Decimal precision and scale are now emitted correctly in the DbType attributes for stored procedures
• Foreign key changes will be picked up when bringing tables back into the designer without a restart

Code generation (SQL Metal + LINQ to SQL class designer)

• Stored procedures using original values now compiles when the entity and context namespaces differ
• Virtual internal now generates correct syntax
• Mapping attributes are now fully qualified to prevent conflicts with user types
• KnownTypeAttributes are now emitted for DataContractSerializer with inheritance
• Delay-loaded foreign keys now have the correct, compilable, code generated
• Using stored procedures with concurrency no longer gets confused if entities in different namespace to context
• ForeignKeyReferenceAlreadyHasValueException is now thrown if any association is loaded not just the first

Potentially breaking changes

We worked very hard to avoid breaking changes but of course any
potential bug fix is a breaking change if your application was
depending on the wrong behavior. The ones I specifically want to call
out are:

Skip(0) is no longer a no-op

The special-casing of 0 for Skip to be a no-op was causing some
subtle issues such as eager loading to fail and we took the decision to
stop special casing this. This means if you had syntax that was invalid
for a Skip greater than 0 it will now also be invalid for skip with a
0. This makes more sense and means your app would break on the first
page now instead of subtlety breaking on the second page. Fail fast :)

ForeignKeyReferenceAlreadyHasValue exception

If you are getting this exception where you weren’t previously it
means you have an underlying foreign key with multiple associations
based on it and you are trying to change the underlying foreign key
even though we have associations loaded.Best thing to do here is to set
the associations themselves and if you can’t do that make sure they
aren’t loaded when you want to set the foreign key to avoid
inconsistencies.

Often we have to get a count of duplicate items in a list. An easy way to do this is to group the list on the property we want to count, then use LINQ's GroupBy's Count feature. Below is a quick example using an employee list.

private static void Main()
{
    List<Employee> empList = new List<Employee>();

    empList.Add(new Employee() { ID = 1, FName = "John", Age=23, Sex='M'  });
    empList.Add(new Employee() { ID = 2, FName = "Mary", Age = 25, Sex = 'F' });
    empList.Add(new Employee() { ID = 3, FName = "Amber", Age = 23, Sex = 'M' });
    empList.Add(new Employee() { ID = 4, FName = "Kathy", Age=25, Sex='M'});
    empList.Add(new Employee() { ID = 5, FName = "Lena", Age=27, Sex='F'});
    empList.Add(new Employee() { ID = 6, FName = "John", Age = 28, Sex = 'M' });
    empList.Add(new Employee() { ID = 7, FName = "Kathy", Age = 27, Sex = 'F' });
    empList.Add(new Employee() { ID = 8, FName = "John", Age = 28, Sex = 'M' });

    var dup = empList
        .GroupBy(x => new { x.FName })
        .Select(group => new { Name = group.Key, Count = group.Count() })
        .OrderByDescending(x => x.Count);

    foreach (var x in dup)
    {
        Response.Write(x.Count + " " + x.Name);
    }
}

class Employee
{
    public int ID { get; set; }
    public string FName { get; set; }
    public int Age { get; set; }
    public char Sex { get; set; }
}

In this
article, I will cover how to access data in SQL Server Compact
databases (.sdf file) using new development technologies such as LINQ.
LINQ is the new initiative of Microsoft to support Object-Relational
Mapping concepts and design patterns. LINQ provides a full type-safety
and compile-time checking of query expressions in order to minimize the
object-relational concepts mismatch and enables managing relational
data as objects providing an easy way to integrate data validation and
business logic rules into your application.

Figure 1

Figure 2

Figure 3

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace LINQ_SQLCompact
{
    class Program
    {
        static void Main(string[] args)
        {
            string strConnString = "Northwind.sdf";
            Northwind dbNorthwind = new Northwind(strConnString);
 
            var query = from c in dbNorthwind.Customers
                        where c.City == "Paris"
                        select c;
 
            foreach (Customers c in query)
            {
                System.Console.WriteLine("ContactName={0}, Address={1}, City={2}",c.ContactName, c.Address, c.City);
            }
 
            System.Console.WriteLine("Press any key to finish …");
            System.Console.Read();

       }

    }

}

Sometimes you have a list, and you need to return a single element from the list. There are several ways to get the element to return. Below are two ways using LINQ with Lambda expressions.

Consider the following class:

   1: public class Person

   2: {

   3:     public string Name { get; set; }

   4:     public int Age { get; set; }

   5:     public bool Leader { get; set; }

   6: }

And let's load up some sample data:

   1: Person[] people = new Person[] {

   2:     new Person { Name = "Blue", Age = 25, Leader = true },

   3:     new Person { Name = "Gold", Age = 16, Leader = false },

   4:     new Person { Name = "Red", Age = 27, Leader = false },

   5:     new Person { Name = "Green", Age = 14, Leader = false}

   6: };

Now what we could do to find the leader (the assumption is that there is always only one leader):

   1: Person leader = people.Where(p => p.Leader == true).ToArray()[0];

The result of the people.Where() is an IEnumerable<Person>. 
And you can't just index the first element of that – so you convert it
to an array and index that instead.

LINQ provides two methods to perform this type of query without the
need of having an intermediate array — "First" and "Single":

   1: Person leader2 = people.First(p => p.Leader == true);

   2: Person leader3 = people.Single(p => p.Leader == true);

The difference between the two is that First grabs the first item it
finds.  The Single method expects only a single matching item and will
throw an exception if it finds more than one.  In this case, there is
only one Person in the array that has Leader set to true so both of
these lines of code produce the same result.

However, in the situation below:

   1: Person firstChild1 = people.First(p => p.Age < 18);

   2: Person firstChild2 = people.Single(p => p.Age < 18);

The first line will succeed.  The second line will fail since there are two people that are under 18.

Here's a simple example to show you how to GroupBy Multiple Values
using LINQ. In this example, I am grouping by Age and Sex to find the
count of people who have the same age and sex.

C#

VB.NET

Partial Public Class LINQ

    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)

        Dim empList As New List(Of Employee)()

        empList.Add(New Employee() With {.ID = 1, .FName = "John", .Age = 23, .Sex = "M"c})

        empList.Add(New Employee() With {.ID = 2, .FName = "Mary", .Age = 25, .Sex = "F"c})

        empList.Add(New Employee() With {.ID = 3, .FName = "Amber", .Age = 23, .Sex = "M"c})

        empList.Add(New Employee() With {.ID = 4, .FName = "Kathy", .Age = 25, .Sex = "M"c})

        empList.Add(New Employee() With {.ID = 5, .FName = "Lena", .Age = 27, .Sex = "F"c})

        empList.Add(New Employee() With {.ID = 6, .FName = "Bill", .Age = 28, .Sex = "M"c})

        empList.Add(New Employee() With {.ID = 7, .FName = "Celina", .Age = 27, .Sex = "F"c})

        empList.Add(New Employee() With {.ID = 8, .FName = "John", .Age = 28, .Sex = "M"c})

        Dim sums = empList.GroupBy(Function(x) New With {Key x.Age, Key x.Sex}).Select(Function(group) New With {Key .Peo = group.Key, Key .Count = group.Count()})

        For Each employee In sums

            ' use employee.Count and employee.Key

        Next employee

    End Sub

    Public Class Employee

        Private privateID As Integer

        Public Property ID() As Integer

            Get

                Return privateID

            End Get

            Set(ByVal value As Integer)

                privateID = value

            End Set

        End Property

        Private privateFName As String

        Public Property FName() As String

            Get

                Return privateFName

            End Get

            Set(ByVal value As String)

                privateFName = value

            End Set

        End Property

        Private privateAge As Integer

        Public Property Age() As Integer

            Get

                Return privateAge

            End Get

            Set(ByVal value As Integer)

                privateAge = value

            End Set

        End Property

        Private privateSex As Char

        Public Property Sex() As Char

            Get

                Return privateSex

            End Get

            Set(ByVal value As Char)

                privateSex = value

            End Set

        End Property

    End Class

End Class

Twitter feeds are provided in RSS XML format. This makes it very easy for us to parse out the information we want from a feed using LINQ to XML. For example, if we want to grab the message and date of each Twitter entry, we could use something like this:

public class Twitter
{
    public string Message { get; set; }
    public DateTime PubDate { get; set; }

    public static List<Twitter> Parse(string User)
    {
        var rv = new List<Twitter>();
        var url = "http://twitter.com/statuses/user_timeline/" + User + ".rss";
 
        var element = XElement.Load(url);
        foreach (var node in element.Element("channel").Elements("item"))
        {
            var twit = new Twitter();
            var message = node.Element("description").Value;

            //remove username information
            twit.Message = message.Replace(User + ": ", string.Empty);
            twit.PubDate = DateTime.Parse(node.Element("pubDate").Value);
            rv.Add(twit);
        }

        return rv;
    }
}

 

You can get the twitter feeds by using the following:

		var fromTwitter = Twitter.Parse("Merlin981");
	

Here is a code snippet which accomplishes these following goals:

• It marries a C# Regular Expression MatchCollection to a property list using Linq.
• It uses a Regex Pattern which creates named capture groups which Linq can easily exploit in the join of two data lists.

Let me show you the code. Don’t get hung up on the pattern or what it is doing. What needs to be known is that the pattern places the data matched into Named Capture Groups of Key and Value. The actual Key value corresponds to a property on a real class. Using reflection we will find that property on the class and link its property name to the value stored. That will allow us to change that properties value on the class from the Value we get from the regex match.

The goal of the Linq code is to join into another list, which is the list of properties from the class and the commonality is the PropertyInfo.Name found in that list. Once that data is joined a new object will be created which will have the actual property object and the value of Value. That new list will allow the following operations to set target properties value to Value of the match in Match collection.

   1: public static T ASCIISerializeOut<T>( string targetSerialized )

   2:     where T : new()

   3: {

   4:

   5:     T targetInstance = new T();

   6:

   7:     string pattern = string.Format( @"(?<Key>[^{0}]*)(?:{0})(?<Value>[^{1}]*)(?:{1}?)",

   8:                                     Seperators.cnKVPSeperator,   // "±"

   9:                                     Seperators.cnSeperator );    // "¶"

  10:

  11:     MatchCollection mcKVPs = Regex.Matches( targetSerialized,

  12:                                             pattern,

  13:                                             RegexOptions.Compiled );

  14:

  15:     var kvps = from Match m in mcKVPs

  16:                where mcKVPs != null

  17:                where mcKVPs.Count > 0

  18:                join prp in GetPublicProperties<T>() on m.Groups["Key"].Value equals prp.Name

  19:                select new

  20:                {

  21:                    prop  = prp,

  22:                    Value = m.Groups["Value"].Value ?? string.Empty

  23:                };

  24:

  25:     foreach (var item in kvps)

  26:         item.prop.SetValue( targetInstance, item.Value, null );

  27:

  28:     return targetInstance;

  29:

  30: }

  31:

  32: /// <summary>

  33: /// Return all public properties which are of string type from T class.

  34: /// </summary>

  35: public static IEnumerable<PropertyInfo> GetPublicProperties<T>()

  36: {

  37:     return from p in typeof( T ).GetProperties()

  38:            where p.PropertyType == typeof( string )

  39:            select p;

  40: }

• Line 01: The function takes in text such as “AProp±AValue¶BProp±BValue” which needs to be serialized into a newly created class of type T. The first item in the pattern is the property name AProp followed by a seperator ± then the value of the property AValue and finally a key value seperator: ¶. our regex will create individual matches for each of the key value pairs.
• Line 07: This pattern when used will get key and value pair combinations and place them in named groups of Key and Value of the match.
• Line 11: Get all the key/value pair combinations into the match collection.
• Line 15: Linq starts here: We define a Var object kvps (key value pairs) which will use /loop each match from the match collection.
• Line 16: Make sure the collection is not null.
• Line 17: Make sure there are one or more matches.
• Line 18: Get all the public properties of class T and make a join to our collection data. Key should match the property Name.
• Line 19: Each match found within the property where the names are the same will create this new object below with two properties.
• Line 21: Save the actual property object, we need that later to load data.
• Line 22: Get the value out of the Value group and save that as well. Note, if it is null, just use string.Empty. Thanks Null Coalescing operation (??).
• Line 25: Now for each var object created enumerate through it and load the target values into our newly minted class object of T.
• Line 26: Set the target item’s property to the value found from the regex matches.
• Line 28: Return the new object with the original text data serialized in.
• Line 35: Return an enumeration of all generic string properties of the type T.

Undoubtedly,
anyone who has evaluated LINQ to SQL has fond it a fairly powerful yet
lightweight ORM technology which is less complex than the ADO Entity
Framework yet utilizes the strength and power of Language Integrated
Queries.

One problem with LINQ to SQL is the auto paging
feature of the LinqDataSource.  Below is a rough GridView which
displays three columns, UserName, FirstName and LastName.  This is just
a rough demo, so we’re looking at paging.

If
you simply drop a GridView and a LinqDataSource control onto a Web Form
and configure the LinqDataSource (using Smart Tags) without specifying
a Group By field or Order By field (Figure 1) then you will get fairly
optimal database querying (Figure 2) although without any ordering.

      
     Figure 1: Configure Linq Data Source

      
     Figure 2: SQL Trace of a page load

While
it is great for lightweight or simple applications, it’s rather
unacceptable for use in anything serious (especially with more complex
queries). 

Should you supply a Group By/Order By field,
the LinqDataSource control will query for a RowCount before executing a
single query for each row in the range, i.e. if Page Count is 10, it
will execute 10 queries after the initial row query (Figure 3). 

    
     Figure 3: SQL Trace when using Group By

This occurs when you choose to use a group by/order by sort – The select clause of the LinqDataSource becomes:

     GroupBy="UserName" OrderGroupsBy="key" Select="new (key as UserName, it as Users)"

Obviously we’d like decent performance and
the ability to sort/group our data.  So there is a pretty low-overhead
solution.  The first (obvious) step is to disable the LinqDataSource
AutoPage property.  The next is to implement an event for
LinqDataSource’s OnSelecting event, as so:  (note: I’ve included the
GridView for reference)

<form id="form1" runat="server">

  <asp:GridView ID="UserGridView" runat="server" AllowPaging="True"
      DataSourceID="UserLinqDataSource" AutoGenerateColumns="False">
      <Columns>
          <asp:BoundField DataField="UserName" HeaderText="UserName" ReadOnly="True"
              SortExpression="UserName" />
               <asp:BoundField DataField="FirstName" HeaderText="FirstName"
              SortExpression="FirstName" />
               <asp:BoundField DataField="LastName" HeaderText="LastName"
              SortExpression="LastName" />
      </Columns>
  </asp:GridView>

  <asp:LinqDataSource ID="UserLinqDataSource" runat="server" AutoPage="False"
      ContextTypeName="DataAccess.DataClassesDataContext"
      OnSelecting="UserLinqDataSourceSelecting"
      Select="new (UserName, FirstName, LastName)" TableName="Users">
  </asp:LinqDataSource>

</form>

Now for our code behind, we only need to implement the Selecting event, and we have our smarter paging enabled.

                /// <summary>
        /// Implements Server Side Paging for the LinqDataSource
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void UserLinqDataSourceSelecting(object sender, LinqDataSourceSelectEventArgs e)
        {
            /*
             When AutoPage is false, LinqDataSource requires that the user handle the paging
             manually during the Selecting event.  In this case, you need to set
             DataSourceSelectArguments.TotalRowCount yourself and perform the paging manually
             using DataSourceSelectArguments.StartRowIndex and            
             DataSourceSelectArguments.MaximumRows (pageSize).
            */         

            e.Arguments.StartRowIndex = 0;
            e.Arguments.MaximumRows = 10;                //add your paging limit requirement here
            DataClassesDataContext dc = new DataClassesDataContext();

            e.Arguments.TotalRowCount = dc.Users.Count();   //you could store this value or cache
                                                            //it to avoid the extra DB hit

            //uses an example of ten records/page modify to fit your own paging
            //requirements                                
            e.Result = (from i in dc.Users select i).Skip(UserGridView.PageIndex * 10).Take(10);

            //uses Linq’s Skip() and Take() functions to select a sub section
        }

Let’s take a look at the SQL profile trace, just to be sure:

    
     Figure 4: New SQL Trace with custom paging

So
this is just a simple little scenario.  It’s nothing big.  It might
help some people out there who are looking to use LinqDataSource but
don’t like the overhead of the Auto Paging.  You’d probably also need
to take a look at supporting sorting (which would need to be factored in to the custom paging query).

This entry doesn’t discuss management of Data Contexts
or a plethora of other considerations which you should take into
consideration before implementing a solution.  Please plan ahead
accordingly before choosing an approach.