Is LINQ overspecifying my datetime values?  Check this out:

declare @p0 datetime
select @p0='2010-02-13 08:09:22.2270000'
Msg 241, Level 16, State 1, Line 4
Conversion failed when converting date and/or time from character string.

Compare to:

declare @p0 datetime
select @p0='2010-02-13 08:09:22'
Command(s) completed successfully.

The first set of datetime varchar strings is what is generated by my LINQ to SQL code.  But SQL Server barfs on it.  Hm.

Why have I not used LINQPad before?  OMG.  This tool is a must-have for C# developers.

What is LINQPad?

It’s an IDE that allows you to run LINQ queries interactively.  (It does much, much more than that as well)

Who developed LINQPad?

Joseph Albahari.  He’s written a few books on LINQ/C#/WPF, so he knows what the hell he’s talking about.

Show me an example!

OK – here’s a simple example from a query I was killing myself over the other day:

I’ll point out the following:

• Real-time results!  No more fix-compile-run-debug cycle as you’re developing your LINQ queries.
• You can run your queries in “C# statement” mode, which is essentially like running them in an application.  You can also choose “C# expression” mode, which is more lightweight and doesn’t require semicolons, assignments, etc.
• You can use VB.NET if you want, in addition to C#.
• You can run your LINQ-to-SQL queries directly against your database, as I’m doing in the above example.
• You can import your own .NET assemblies and reference them.  So, in the above example, instead of hardcoding the number 3 in my query, I could have referenced my zzzzz.library.dll assembly, and referenced my CourseType enumeration instead.
• LINQPad has this special .Dump() method which spits out its referent to the results pane.
• You can use the SQL tab to see what kind of SQL query your LINQ will create.
• Just like any IDE, you can save your files (with a default .linq extension) and come back to them later.
• LINQPad comes with a ton of examples, mostly pulled from the book C# 3.0 In A Nutshell.

A couple (minor) downers:

• LINQPad does an automatic uppercase conversion of the first letter of your DB object names.  I think this is to avoid possible name collisions with C#, but it does mean you may have to do some case-cleanup prior to moving your code over.
• LINQPad is free (yay!) but the autocompletion option, which, if you’re a Visual Studio user, you’ve gotten used to, is $29 extra.  I suppose I can’t complain about that – he has to make a living, and the free tool is VERY powerful even without the autocomplete.

You should definitely go check out this tool.  I’ve only picked it up 12 hours ago and am already smitten.

Was messing around last night with LINQ and struggling for a few minutes with OUTER JOIN syntax, but finally figured it out.  This is one situation where the normal “intuit your way through it” method based on applying the lessons of experience doesn’t work. (Of course, if I were good at taking lessons from my own experience…well, that’s another story).  Here’s the LINQ for a simple query that solves this problem:

Given a SCHOOL, give me the school name and the count of ENROLLMENTS for which the drop_date column is not null.

var ens = ( from s in adc.schools
                            join e in adc.enrollments on s.school_id equals e.school_id
                            into fullschools
                            from x in fullschools.Where(y=>y.drop_date != null).DefaultIfEmpty()
                            group x by
                                   new
                                   {
                                       SchoolName = s.school_name,
                                       Count=fullpayorgs.Count(z=>z.drop_date != null)
                                   }
                               into g
                            select new { SchoolName=g.Key.SchoolName,
                                        Count=g.Key.Count }
                                        ).OrderBy(y=>y.SchoolName);

Whew!  Here’s the equivalent T-SQL:

select s.school_name, count(en.enrollment_id)
from school s
LEFT JOIN enrollment en ON (s.school_id = en.school_id and en.drop_date is not null)
GROUP BY s.school_name
order by s.school_name

A couple notes are in order:

The LEFT OUTER JOIN is accomplished by the second from clause, combined with the .DefaultIfEmpty() method call.  Sounds screwy, I know.

The effect of having two clauses in the JOIN statement, as in the T-SQL example, is  accomplished by doing one clause in the original join on adc.enrollments, and one clause in the .Where() method call on the fullschools interim collection.

You might be asking why I add the lambda expression z=>z.drop_date != null in the Count() statement, especially since I already have that lambda earlier in the fullschools.Where() call. Without it, LINQ would do a count(*) in the T-SQL output, which would pick up the original SCHOOL record, and give me an incorrect count.  What I really need is just the count of the ENROLLMENT records (if any), so I need it to output something like count(e.enrollment_id).  What it actually does is spit out a horrendous mess of nested SELECT statements, which rubs me like steel wool (i.e. not good), but there are probably optimization techniques I’m not yet familiar with.

My first experience with functional programming was a Lisp class I took around ’92 or ’93. I thought all those parenthesis were irritating, but more than that, the slipperiness of the whole functional paradigm was – well, frustrating partly describes it, but gratuitous is another way I’d describe it. Who needed it?

Then began a happy interlude of 15 years of procedural/imperative programming – Ada, VB, C, C++, Java, C#, SQL, etc. Ahhhhh.

Now, functional programming is all the rage. You’ve got Scheme, Erlang, Haskell, etc. And when Linq came out, I’ve been seeing all these things like Dynamic Lambda Expressions, parse trees, Func<> delegates, etc. It’s like I’ve been tossed off the dock at age 6 all over again.

But having spent some time getting acquainted today, it’s not all that problematic. It’s a shift in perspective. And it definitely helps to have specific problems to solve. Case in point: trying to apply Linq Dynamic Extensions to be able to take a strongly-typed predicate like:

OrderBy(c => c.Name);

and turning it into something more dynamic, like:

OrderBy("name");

…the better to be able to generate Linq results on the fly without having to paint yourself in a switch() corner. There’s a set of Dynamic Extensions available in this zip file that allows you to do just that sort of thing. Unusually for Microsoft, they don’t give you a step-by-step guide to actually implement it. The steps turn out to be pretty easy:

Copy the Dynamic.cs file to your project (I put it next to my .dbml file in my library project).

Add this statement to the class file in which you want to use the Dynamic Query functionality:

import System.Linq.Dynamic;

Then you can go ahead and so something like:

var p = (from s in Subject select s).OrderBy("name");

I’m pretty sure that when I get a free minute I’ll really enjoy getting into the functional programming theory in a more leisurely manner. Right now I have projects to get out the door, so I must be more workmanlike by necessity.

I was doing some Linq programming and kept hitting the problem where if there were no results to a query, but I wanted to call First() or Single() to get a single row, I kept getting “Sequence contains no elements” exceptions.

There didn’t seem to be an easy way to tell if the resultset was null, because it wasn’t really NULL, not in a C# sense. And trying to get Count() would throw a separate exception if there really was at least one element, because I couldn’t enumerate a sequence more than once.

Luckily an offhand comment by Scott Guthrie on his blog helped me out a lot. Use FirstOrDefault() with a dummy criteria as an argument:

var record = <blah>.GetResult().FirstOrDefault(p => p.id <0)

If there are no results, this code will return NULL to the variable, which you can then easily check in your code.

Thanks Scott!

UPDATE2: OK, I figured this one out. Wow!

The problem had to do with WinSpace’s GetXsdType() function. In my database table, I am using a rowversion (aka timestamp) column per reading Rick Stahl’s weblog dealing with serializing and updating LINQ entities, and the generated DBML data type was “System.Data.Linq.Binary”, which wasn’t accounted for in that GetXsdType() function.

There was some groping around while I determined how to convert the rowversion value (ex: “0000000000000BBB”) to the correct output value in the XML. Which native XSD type to use? I am no expert with schemas, serialization, binary, etc. so I tried hexBinary and base64Binary before finally settling on long as per this MSFT article. Then I created a little custom function to convert the LINQ value to the appropriate hex string using the System.Data.Linq.Binary.ToArray() method.

Now I get the Version output from the web service as follows:

<Version>0000000000000BBB</Version>

Voila! A day gone, but I learned a lot.

UPDATE: This is a thorny one. Here are the debugging steps I’ve taken so far:

• First, I ran WinSpace’s code separately. I had to download the Northwind sample database from Microsoft. That worked just fine.
• On the theory that my code was not including some required serialization routines, I commented out just about everything I could in WinSpace’s code, but his still worked and mine didn’t.
• On the theory that it had to do with his code running in a Website project while mine was in a Web Application Project (which referenced a Class Library), I created my own test Website project. Still no go.
• On the theory that it had something do to with the database, I created a LINQ-to-SQL .dbml file for the Northwind database in my own application. That worked!

Huh. So now I’m down to figuring out what it is about the generated .dbml files that are different.

Continue Reading »

See if you can spot the contradiction in the following two FAQs:

Q: Should I create a new DataContext in every business logic method?
A: The DataContext conforms to the Unit of Work design pattern. Unless you are moving data between physical tiers between each operation you should keep your DataContext alive for the duration of work.
Q: Should I keep my DataContext in a static/global/shared variable?
A: The DataContext is not thread safe and is not meant to be shared. A DataContext is meant to be used for a single unit or work or at most for multiple consecutive units of work.

Hint: I’ve highlighted it for you.

It’s immediately apparent from even 30 minutes going through VS 2008 / .NET 3.5 that I need to learn about LINQ, which means I need to bone up on C# generics.  I’ve been avoiding generics for a while, in favor of strongly typed custom collection classes that implement the IEnumerable/IEnumerator stuff, which gives me a lot of control and means I don’t need, nor get, everything that generics give me in order to satisfy the customer requirements.

LINQ just looks too cool to pass up for any length of time, which means it’s back to school for Generics.