LINQ Exchange

This video, courtesy of Microsoft’s ASP.NET video series, provides an example of using the LINQ to SQL data model for developing applications with LINQ to SQL.

This video, courtesy of Microsoft’s ASP.NET video series, provides an overview of LINQ to SQL, and how it can change the way you develop applications.

I’m working on redesigning the site. I’ve tried to put redirects up for every page that has moved (I moved from BlogEngine.NET to WordPress, so the files are named differently). If you see any missing images or files, please let me know.

Thanks.

There are many little tips and tricks for LINQ to SQL which can make our lives easier, or improve the speed and efficiency of the generated code. Below are several tips and tricks I have found during my time as a LINQ developer.

*Note: Some of these tips are for querying directly from the table. While I'm a big advocate of always using stored procedures, there are circumstances where that may not be possible or desirable.*

Loading a delay-loaded property

LINQ to SQL lets you specify that a property is delay-loaded meaning
that it is not normally retrieved as part of normal query operations
against that entity. This is particularly useful for binary and large
text fields such as a photo property on an employee object that is
rarely used and would cause a large amount of memory to be consumed on
the client not to mention traffic between the SQL and application.

There are times however when you want all these binaries returned in
a single query, say for example returning all the photos for the
company photo intranet page:

	var db = new NorthwindContext();
	var loadOptions = new DataLoadOptions();
	loadOptions.LoadWith<Employee>(e => e.Photo);
	db.LoadOptions = loadOptions;
	

Intercepting create, update and delete operations

There are times it is useful to be able to listen in to when these
events happen and perform your own logic, perhaps auditing or logging
for some scenarios. The easiest way to do this is to implement some
specially-named methods on your data context, perform your action and
then to dispatch the call back to LINQ to SQL.

The format of these specially-named methods is [Action][Entity] and
then you should pass back control to LINQ to SQL using
ExecuteDynamic[Action] where [Action] is either Insert, Update or
Delete. One example of such usage might be:

partial class NorthwindContext {
partial void InsertEmployee(Employee instance) {
instance.CreatedBy = CurrentUser;
instance.CreatedAt = DateTime.Now;
ExecuteDynamicInsert(instance);
}
partial void UpdateEmployee(Employee instance) {
AuditEmployeeOwnerChange(instance);
instance.LastModifiedAt = DateTime.Now;
ExecuteDynamicUpdate(instance);
}
partial void DeleteEmployee(Employee instance) {
AuditDelete(instance, CurrentUser);
ExecuteDynamicDelete(instance);
}
}

Take full control of  the TSQL

There are times when LINQ to SQL refuses to cook up the TSQL you
wanted either because it doesn’t support the feature or because it has
a different idea what makes an optimal query.

In either case the Translate method allows you to deliver your own
TSQL to LINQ to SQL to process as if it were its own with execution,
materialization and identity mapping still honored. For example:

var db = new PeopleContext();
if (db.Connection.State == System.Data.ConnectionState.Closed)
db.Connection.Open();
var cmd = db.GetCommand(db.Persons.Where(p => p.CountryID == 1));
cmd.CommandText = cmd.CommandText.Replace("[People] AS [t0]", "[People] AS [t0] WITH (NOLOCK)");
var results = db.Translate<Person>(cmd.ExecuteReader());

Complex stored procedures

When working with stored procedures the LINQ to SQL designer and
SQLMetal tools need a way of figuring out what the return type will be.
In order to do this without actually running the stored procedure
itself they use the SET FMTONLY command set to ON so that SQL Server
will just parse the stored procedure instead.

Unfortunately this parsing does not extend to tdynamic SQL or
temporary tables so you must change the return type from the scalar
integer to one of the known entity types by hand. You could use the
following command at the start to let it run regardless given the
subsequent warning.

SET FMTONLY OFF

If your stored procedure can not safely handle being
called at any time with null parameters set the return type by hand
instead.

Cloning an entity

There are many reasons you might want to clone an entity – you may
want to create many similar ones, you could want to keep it around
longer than the DataContext it came from – whatever your reason
implementing a Clone method can be a pain but taking advantage of the
DataContractSerializer can make light work of this providing your DBML
is set to enable serialization.

public static T Clone<T>(T source) {
var dcs = new System.Runtime.Serialization.DataContractSerializer(typeof(T));
using (var ms = new System.IO.MemoryStream()) {
dcs.WriteObject(ms, source);
ms.Seek(0, System.IO.SeekOrigin.Begin);
return (T)dcs.ReadObject(ms);
}
}

And then to clone simply:

var source = myQuery.First();var cloned = Clone(source);

Be aware that this comes with a little overhead in the serialization and deserialization process.

LINQ OfType<> is a useful filtering function for returning items in a collection which match a specific type (or base type). An example might be for getting base items from a listbox of combobox.

An example without OfType:

ColorWrapper x = combo.Items.Where( obj => obj is ColorWrapper ).Cast< ColorWrapper >().FirstOrDefault(cw => cw.Color == value);

An example of LINQ OfType:

ColorWrapper y = combo.Items.OfType<ColorWrapper>().FirstOrDefault(cw => cw.Color == value);

I've been doing a lot of mult-threading work, recently, using the standard Thead class, the Worker Queue, and the new PLINQ (Parallel LINQ). The problem with most of the built-in generic collections (Queue<>, List<>, Dictionary<>, etc), is that they are not thread safe.

I created a library of thread safe collections which allow me to use the standard generic collection actions (foreach, LINQ, etc), while at the same time being thread safe.

The classes in this library inherit from the appropriate collection interface (IEnumerable, ICollection, etc). Each class also has all the functions and properties that it's original non-thread safe class has.

You can download a copy of the entire library, which includes support for a thread safe List<>, Dictionary<>, and Queue<>, here: Thread Safe Generic Collections

TQueue<> Example:

The first thing we need to do is create a container for the TQueue and a thread lock object. I generally prefer to use the ReaderWriterLockSlim because it is light weight and fast.

Just like a standard Queue, we have three overloads for the Initialization. These overloads allow an empty Queue to be created, a Queue with a specified capacity, or a Queue with an initial IENumerable collection to populate the Queue.

This next function is probably the most important one. The GetEnumerator() is used during ForEach loops, and returns the next item in the collection. Following Microsoft's example of a thread-safe enumerator, we first get a copy of the current container Queue, then use this copy for iterating. You'll notice the use the Read lock before acquiring the container Queue copy.

A Queue must include an Enqueue and a Dequeue, used for adding and removing items from the collection. Just as in every other function, we're using the locks to protect our data access.

I found that many times I have a need to enqueue multiple items at once. This lead to the creation of the EnqueueAll functions. You'll notice the second overload is using the thread safe List (TList).

And, since we have an EnqueueAll, I also found a need to dequeue everything at once. DequeueAll returns a thread safe list (TList), instead of the standard List.

Today I will talk about a simple and useful LINQ method called "TakeWhile". Enumerable.TakeWhile as defined on MSDN is a method that returns elements from a sequence as long as a specified condition is true, and then skips the remaining elements. To show an example use of it, I provided a sample code that retrieve all numbers from a list that are less than 10 until it encounters a number greater or equal to 10.

int[] intnum = { 1, 3, 5, 7, 9, 11, 8, 9, 1, 2 };

var lessthan10 = intnum.TakeWhile(x => x < 10);

Console.WriteLine("Numbers that are less than 10.");
foreach (var x in lessthan10)
{
    Console.WriteLine(x);
}

The LINQ code above will produce the following result:

1
3
5
7
9

You can also apply it on a string list, as shown below.

string[] employees = { "auric", "rizza", "jayson", "fryan", "james", "cheesy" };

IEnumerable query =
    employees.TakeWhile(employee => String.Compare("fryan", employee, true) != 0);

foreach (string employee in query)
    Console.WriteLine(employee);

The LINQ code above will produce the following result:

auric
rizza
jayson

Anyone using LINQ to manipulate in-memory collections is probably
also using plenty of lambda expressions to make things quite easy.
These two additions were really meant for each other. One of our
interns here recently ran into an interesting problem while using LINQ.
As a relatively new user of .NET based languages, reference types
caused him a bit of trouble.

The problem

While using the dot notation with lambda expressions, he was using the Except method in the following way.

List<MyObject> x = myCollection.Except(otherCollection).ToList();

Well the problem here is that these two collections contain
"MyObject"s, and when it does the comparison it does so based on the
reference. This means if those are separate but equivalent objects that
the comparison will claim they are different.

He had unit tests making sure that the except statement worked, but was using the same instance of variables to Assert, so the tests claimed to work.

I told him the problem and mentioned that there was probably an
overload of Except that allows one to specify how to do the comparison.
I was correct, but the overload takes an IEqualityComparer object. I
was hoping for a Func<x,x,bool> as the second parameter, so I did
what I always do; I Googled to see if anyone knew an easy way to get
that to work without doing extra work.

The Internet was kind enough to inform me that there was no built in way of handling this situation.

Building your own was the suggestion. It is a pretty simple class,
so it can just be tossed somewhere to be reused easily. It could easily
come up and be needed again.

public class LambdaComparer<T> : IEqualityComparer<T>
{
private readonly Func<T, T, bool> _lambdaComparer;
private readonly Func<T, T, bool> _lambdaHash;
public LambdaComparer(Func<T, T, bool> lambdaComparer) :
this(lambdaComparer, o => o.GetHashCode())
{
}
public LambdaComparer(Func<T, T, bool> lambdaComparer, Func<T, int> lambdaHash)
{
if (lambdaComparer == null)
throw new ArgumentNullException("lambdaComparer");
if (lambdaHash == null)
throw new ArgumentNullException("lambdaHash");
_lambdaComparer = lambdaComparer;
_lambdaHash = lambdaHash;
}
public bool Equals(T x, T y)
{
return _lambdaComparer(x, y);
}
public int GetHashCode(T obj)
{
return _lambdaHash(obj);
}
}

Now that we have a nice, Generic, comparer which can take lambda
expressions, we are all set to plug this in to the previous code.

List<MyObject> x = myCollection.Except(otherCollection,
new LambdaComparer<MyObject>((x, y) => x.Id == y.Id)).ToList();
// or
IEqualityComparer comparer = new LambdaComparer<MyObject>((x, y) => x.Id == y.Id);
List<MyObject> x = myCollection.Except(otherCollection, comparer).ToList();

I admit I am still kind of annoyed that there wasn't an overload which just took a Func<T, T, bool> or a Func<T, T, int>.

An alternative is to use this extension method so you can hide away the fact that you're using the custom comparer class.

public static class Ext
{
public static IEnumerable<TSource> Except<TSource>(this IEnumerable<TSource> first,
IEnumerable<TSource> second , Func<TSource, TSource, bool> comparer )
{
return first.Except(second, new LambdaComparer<TSource>(comparer));
}
}

I know the title is a bit long, but it descriptive of the problem.
Recently I had need to retrieve a listing of files and folders given a
root path. All worked well, until I tested on Vista and Windows 7
machines. When trying to browse the "Users" folder, I kept getting
access denied errors and no lists returned. The problem is that
Directory.GetFiles and Directory.GetDirectories will fail on the first
sign of an access denied issue. On Vista and Windows 7, most of the
directories under the Users folder are ACL'd to not allow reading or
browsing.

The solution is to use a DirectoryInfo object to get the list of
directories, and loop through those. The solution I present below uses
PLINQ (Parallel LINQ) for multi-threaded looping. If you do not have
PLINQ installed, simply change the Parallel.ForEach statements to the
standard ForEach.

Notice the use of a Predicate in both functions. This allows you to
add advanced filtering, for example only returning files with an
extension of ".jpg", or only returning files modified in the last five
days.

I was recently asked to help
with a LINQ To SQL query where the resulting T-SQL query was to
have CASE statements. Having CASE statements in T-SQL queries is a
common scenario but how do we it in LINQ To SQL? The solution is simple and straight-forward. As you will see below, using C#'s "Immediate If" will convert into T-SQL CASE statements.

I have created a table called CityWeather. This table has  two
fields: Name and Temperature. Here is the script if you wish to create
the table on your machine.

CREATE TABLE [dbo].[CityWeather](
[Name] [nvarchar](100) NOT NULL,
[Temperature] [decimal](18, 0) NOT NULL
) ON [PRIMARY]

My objective is to get LINQ To SQL to produce a T-SQL statement similar to this.

SELECT
Name,
Temperature,
CASE Temperature
WHEN 30 THEN 'Toasted'
WHEN 25 THEN 'I like it'
WHEN 10 THEN 'Just perfect'
WHEN -15 THEN 'Gonna freeze my'
END AS 'Message'
FROM CityWeather

Below is the C# code I wrote for my LINQ To SQL query.

from c in CityWeathers

select new

        {

          c.Name,

          c.Temperature,

          Messaage = c.Temperature == 30 ? "Toasted" : 

          c.Temperature == 25 ? "I like it" :

          c.Temperature == 10 ? "Just perfect" :

          c.Temperature == -15 ? "Gonna freeze my" : ""

        }

My LINQ To SQL query produced the following T-SQL Query

SELECT [t0].[Name], [t0].[Temperature],
(CASE
WHEN [t0].[Temperature] = @p0 THEN CONVERT(NVarChar(15),@p1)
WHEN [t0].[Temperature] = @p2 THEN CONVERT(NVarChar(15),@p3)
WHEN [t0].[Temperature] = @p4 THEN CONVERT(NVarChar(15),@p5)
WHEN [t0].[Temperature] = @p6 THEN @p7
ELSE CONVERT(NVarChar(15),@p8)
END) AS [Messaage]
FROM [CityWeather] AS [t0]
-- @p0: Input Decimal (Size = 0; Prec = 33; Scale = 4) [30]
-- @p1: Input NVarChar (Size = 7; Prec = 0; Scale = 0) [Toasted]
-- @p2: Input Decimal (Size = 0; Prec = 33; Scale = 4) [25]
-- @p3: Input NVarChar (Size = 9; Prec = 0; Scale = 0) [I like it]
-- @p4: Input Decimal (Size = 0; Prec = 33; Scale = 4) [10]
-- @p5: Input NVarChar (Size = 12; Prec = 0; Scale = 0) [Just perfect]
-- @p6: Input Decimal (Size = 0; Prec = 33; Scale = 4) [-15]
-- @p7: Input NVarChar (Size = 15; Prec = 0; Scale = 0) [Gonna freeze my]
-- @p8: Input NVarChar (Size = 0; Prec = 0; Scale = 0) []
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729