Tag Archives: LINQ

Microsoft Visual Studio 2012 and #disappointing lamda support #coding #programming #csharp

Two things I wanted in 2012, edit and continue on methods containing lambda expressions and also to be able to use them in the watch and immediate windows…

Modifying a statement which contains a lambda expression will prevent the debug session from continuing while Edit and Continue is enabled.

(new Object[] { 1, 2 })
{object[2]}
[0]: 1
[1]: 2
(new Object[] { 1, 2 }).Select(x => x.ToString())
Expression cannot contain lambda expressions

Still disappointed!
Come on Microsoft, two features I’ve wanted since you first introduced lambdas, how long will it take?

Advertisements

LINQ to SQL Transactions

The thing about Transactions in LINQ is that they are rubbish.
It’s all designed around submitting changes to a transaction, great, but if you rollback the transaction those changes can not be submitted a second time, you’d have to manually undo the changes or recreate the DataContext and apply the changes once more.

Below are some examples I put together whilst trying out LINQ to SQL, as they are copied from a Wiki I used to have, please let me know if you spot any errors.

Here’s an example of this with an insert.

VB.NET

Dim testerString As String = "HELLO"
Dim meeee As LINQtoSQLDataClassesDataContext = Nothing

Try
    ' New DataContext
    meeee = New LINQtoSQLDataClassesDataContext() With {.Log = Console.Out}

    ' Open connection
    meeee.Connection.Open()

    ' New transaction
    meeee.Transaction = meeee.Connection.BeginTransaction()

    ' Insert command
    meeee.TableAs.InsertOnSubmit(New TableA() With {.Name = testerString, .Region = "East"})

    ' Submit changes (to the transaction)
    meeee.SubmitChanges()

    ' Rollback
    meeee.Transaction.Rollback()

    ' This would fail on submit because the in memory would have a duplicate - which is annoying but kind of makes sense

    'meeee.Transaction = meeee.Connection.BeginTransaction()
    'meeee.TableAs.InsertOnSubmit(New TableA() With {.Name = testerString, .Region = "West"})
    'meeee.SubmitChanges()
    'meeee.Transaction.Commit()

    ' This would have no effect - changes already submitted, so there are none

    'meeee.Transaction = meeee.Connection.BeginTransaction()
    'meeee.SubmitChanges()
    'meeee.Transaction.Commit()

    ' This would have no effect - once submitted there doesn't appear to be any way back

    'meeee.Transaction = meeee.Connection.BeginTransaction()
    'meeee.Refresh(--of any kind--)
    'meeee.SubmitChanges()
    'meeee.Transaction.Commit()
Finally
    If (Not meeee Is Nothing) Then
        If (Not meeee.Connection Is Nothing) Then
            meeee.Connection.Close()
        End If

        meeee.Dispose()
    End If
End Try

C#

String testerString = "HELLO";
LINQtoSQLDataClassesDataContext meeee = null;

try
{
    // New DataContext
    meeee = new LINQtoSQLDataClassesDataContext() { Log = Console.Out };

    // Open connection
    meeee.Connection.Open();

    // New transaction
    meeee.Transaction = meeee.Connection.BeginTransaction();

    // Insert command
    meeee.TableAs.InsertOnSubmit(new TableA() { Name = testerString, Region = "East" });

    // Submit changes (to the transaction)
    meeee.SubmitChanges();

    // Rollback
    meeee.Transaction.Rollback();

    // This would fail on submit because the in memory would have a duplicate - which is annoying but kind of makes sense

    //meeee.Transaction = meeee.Connection.BeginTransaction();
    //meeee.TableAs.InsertOnSubmit(new TableA() { Name = testerString, Region = "West" });
    //meeee.SubmitChanges();
    //meeee.Transaction.Commit();

    // This would have no effect - changes already submitted, so there are none

    //meeee.Transaction = meeee.Connection.BeginTransaction();
    //meeee.SubmitChanges();
    //meeee.Transaction.Commit();

    // This would have no effect - once submitted there doesn't appear to be any way back

    //meeee.Transaction = meeee.Connection.BeginTransaction();
    //meeee.Refresh(--of any kind--);
    //meeee.SubmitChanges();
    //meeee.Transaction.Commit();
}
finally
{
    if (null != meeee)
    {
        if (null != meeee.Connection)
        {
            meeee.Connection.Close();
        }

        meeee.Dispose();
    }
}

This next part is the retry i.e. ditch it and redo

VB.NET

' Start a clean DataContext if you want to retry / redo the updates

Dim meee2 As LINQtoSQLDataClassesDataContext = Nothing

Try
    meee2 = New LINQtoSQLDataClassesDataContext() With {.Log = Console.Out}
    meee2.Connection.Open()
    meee2.Transaction = meee2.Connection.BeginTransaction()
    meee2.TableAs.InsertOnSubmit(New TableA() With {.Name = testerString, .Region = "South"})
    meee2.SubmitChanges()
    meee2.Transaction.Commit()
Finally
    If (Not meee2 Is Nothing) Then
        If (Not meee2.Connection Is Nothing) Then
            meee2.Connection.Close()
        End If

        meee2.Dispose()
    End If
End Try

C#

// Start a clean DataContext if you want to retry  redo the updates

LINQtoSQLDataClassesDataContext meee2 = null;

try
{
    meee2 = new LINQtoSQLDataClassesDataContext() { Log = Console.Out };
    meee2.Connection.Open();
    meee2.Transaction = meee2.Connection.BeginTransaction();
    meee2.TableAs.InsertOnSubmit(new TableA() { Name = testerString, Region = "South" });
    meee2.SubmitChanges();
    meee2.Transaction.Commit();
}
finally
{
    if (null != meee2)
    {
        if (null != meee2.Connection)
        {
            meee2.Connection.Close();
        }

        meee2.Dispose();
    }
}

Now is an example of a delete:

VB.NET

' Delete it

Dim meee3 As LINQtoSQLDataClassesDataContext = Nothing

Try
    meee3 = New LINQtoSQLDataClassesDataContext() With {.Log = Console.Out}
    meee3.Connection.Open()
    meee3.Transaction = meee3.Connection.BeginTransaction()
    meee3.TableAs.DeleteOnSubmit( _
                meee3.TableAs.Where(Function(x) x.Name = testerString).Select(Function(x) x).FirstOrDefault())
    meee3.SubmitChanges()
    meee3.Transaction.Commit()
Finally
    If (Not meee3 Is Nothing) Then
        If (Not meee3.Connection Is Nothing) Then
            meee3.Connection.Close()
        End If

        meee3.Dispose()
    End If
End Try

C#

// Delete it

LINQtoSQLDataClassesDataContext meee3 = null;

try
{
    meee3 = new LINQtoSQLDataClassesDataContext() { Log = Console.Out };
    meee3.Connection.Open();
    meee3.Transaction = meee3.Connection.BeginTransaction();
    meee3.TableAs.DeleteOnSubmit(
        meee3.TableAs.Where(x => x.Name == testerString).Select(x => x).FirstOrDefault());
    meee3.SubmitChanges();
    meee3.Transaction.Commit();
}
finally
{
    if (null != meee3)
    {
        if (null != meee3.Connection)
        {
            meee3.Connection.Close();
        }

        meee3.Dispose();
    }
}

LINQ basic group (left outer) join

The left outer join is my favourite join, so it’s worth giving it a post of its own…
VB.NET

Dim groupJoinResults = From instA In Me.LINQ2ClassAList _
                       Group Join instB In Me.LINQ2ClassBList _
                       On instA.Region Equals instB.Region _
                       Into groupedBs = Group _
                       Select instA, groupedBs

C#

var groupJoinResults = from instA in this.LINQ2ClassAList
                       join instB in this.LINQ2ClassBList
                       on instA.Region equals instB.Region
                       into groupedBs
                       select new { instA, groupedBs };

In this example the groupedBs are accessible via (result).groupedBs and are themselves var/anonymous types.
groupJoinResults.First().instA.Name = the Name of the first instA returned and
groupJoinResults.First().groupedBs.First().Name = the Name of the first instB that matched it.

In my words a Group Join is a Left Outer Join with the results of the right dumped into an IEnumerable.

LINQ inner join example

Just a quick basic example of a LINQ inner join returning an anonymous type

VB.NET

Dim joinedResults = From instA In Me.LINQ2ClassAList _
                    Join instB In Me.LINQ2ClassBList _
                    On instA.Region Equals instB.Region _
                    Select AName = instA.Name, BName = instB.Name, instA.Region

C#

var joinedResults = from instA in this.LINQ2ClassAList
                    join instB in this.LINQ2ClassBList
                    on instA.Region equals instB.Region
                    select new { AName = instA.Name, BName = instB.Name, instA.Region };

LINQ Basic DataSource binding

This is a quick example of LINQ and binding to a DataGridView control (via DataSource) the property name is used as the column header (Column[idx].HeaderText) this can be change via the following attribute:
System.ComponentModel.DisplayName(“Column header name here”)

The following example uses a LINQ query on a string collection to demonstrate this:

C#

var objCollection = from objs in fileStringCollection
                    select new WrapperForStrings
                    {StringProperty = objs};
this.DataGridView2.DataSource = objCollection.ToList();
this.DataGridView2.AutoResizeColumns();
...
public class WrapperForStrings
{
    private String stringValue;

    [System.ComponentModel.DisplayName("Column header name here C#")]
    public String StringProperty
    {
        get
        {
            return stringValue;
        }
        set
        {
            stringValue = value;
        }
    }
}

VB.NET

Dim objCollection = From objs In fileStringCollection _
                    Select New WrapperForStrings _
                    With {.StringProperty = objs}
Me.DataGridView2.DataSource = objCollection.ToList()
Me.DataGridView2.AutoResizeColumns()
...
Public Class WrapperForStrings
    Private stringValue As String

    <System.ComponentModel.DisplayName("Column header name here VB")> _
    Public Property StringProperty() As String
        Get
            Return stringValue
        End Get
        Set(ByVal value As String)
            stringValue = value
        End Set
    End Property
End Class

LINQ Distinct / Group By

LINQ offers up a nice way to group items and you can even reference the grouping values called keys.

Here’s an example of grouping a collection of Strings based on their length:

VB.NET

' VB requires the = Group part, but Group str By doesn't need the str part.
Dim myList = From str In fileStringCollection _
             Order By str.Length _
             Group By str.Length Into filGrouping = Group

C#

// Of course C# can't have the = Group part, requires the group str by to have the str part and requires the select
var myList = from str in fileStringCollection
             orderby str.Length
             group str by str.Length into filGrouping
             select new { filGrouping = filGrouping, Length = filGrouping.Key };

Here filGrouping behaves like a variable within the scope of the expression, we can look at the distinct values for the Length of the Strings by using the filGrouping.Key. Here we are returning the grouping, if you enumerate the grouping you get each of the Strings for that String Length.

For more on DISTINCT / GROUP BY see my more recent post (from a database point of view) SQL Server / Oracle: DISTINCT or GROUP BY

LINQ Basic Selects

Whether a select is required or not

VB.NET

' Notice no select is required
Dim fileStringCollection = From anythingGoesHere _
                           In My.Computer.FileSystem.GetFiles("C:\")

C#

// Select is required
var fileStringCollection = from anythingGoesHere
                           in Directory.GetFiles(@"C:\")
                           select anythingGoesHere;

Selecting into an object / properties

VB.NET

Dim objCollection = From objs _
                    In fileStringCollection _
                    Select New WrapperForStrings _
                      With {.StringProperty = objs}

C#

var objCollection = from objs
                    in fileStringCollection
                    select new WrapperForStrings
                      {StringProperty = objs};

Returning multiple columns (via an instance of an anonymous class) in a select

VB.NET

' VB can just use a plain syntax as follows
Dim myInfoCollection = From file In filesInfoCollection _
                       Select file.Name, file.CreationTime

C#

// In C# you must explicitly create a new (anonymous) object and explicitly define the properties and values
var myInfoCollection = from file in filesInfoCollection
                       select new { CSName = file.Name,
                         CSCreationTime = file.CreationTime };

Edit: For an example of WrapperForStrings see this post