Simons SQL Blog : LINQ

Entity framework support for table valued functions and thus full text

simonsabin — Sun, 30 Jan 2011 14:12:03 GMT

One of my most popular posts with over 10, 000 hits is how to enable full text when using LINQ to SQL http://sqlblogcasts.com/blogs/simons/archive/2008/12/18/LINQ-to-SQL---Enabling-Fulltext-searching.aspx , core to this is the use of a table valued function...(read more)

Entity Framework how to stop your DBA having a heart attack

simonsabin — Sat, 30 Jan 2010 14:30:32 GMT

My code and presentation from my session at Developer Day 8 are now available here

DDD_8-Entity_Framework Code
Entity framework presentation

Any questions let me know.

Also for more on the new features in Entity Framework v next go to http://blogs.msdn.com/adonet/archive/2009/08/05/improvements-to-the-generated-sql-in-net-4-0-beta1.aspx

Using LINQ with unfriendly collections

simonsabin — Thu, 15 Jan 2009 14:18:25 GMT

I've liked the idea of LINQ in that you can query in memory collections. It saves a huge amount of code if you want to filter users by other criteria.

I'd seen demos and just couldn't figure out how it worked because I was just getting a red suiggly line

"Error 2 Could not find an implementation of the query pattern for source type 'System.Web.Security.MembershipUserCollection'. 'Where' not found. Consider explicitly specifying the type of the range variable 'u'."

Now sometimes you just have a mental block but I couldn't figure out what I needed to do to get it working, and I'm sure some onf you reading will be in "Who wants to be a millionaire" mode and will be telling me exactly what the answer is, its obvious.

Well I'm sure I tried what is the correct solution but to no avail.

Anyway I needed to do this again recently and rather than resort to a for loop and a break statement etc I persevered, and soon realised on reading the error message and taking my time was that a simple LINQ query has implied types, i..e in my example the variable "u" (the range variable) is type based on the items in the collection on the right just like var does. However in this case it can't infer the type and so you have to force the type i.e.

var MatchedUsers = (from MembershipUser u in Membership.FindUsersByEmail("SomeEmail")

where u.UserName != "Smith"

&& u.CreationDate > DateTime.Now.AddDays(-1)

select u);

Now I'm sure this might not be as quick as doing the looping my self, but its a lot easier. Whats more if we want to do something else with this set of users we can do easily rather than having to loop over the whole collection again.

Whats the score with LINQ to SQL?

simonsabin — Sun, 16 Nov 2008 00:16:48 GMT

LINQ to SQL (and any ORM for that matter) has the marmite factor. You either love it or hate it. Whats more many of those that hate it haven't tried it. In many cases the hate it crowd are making a decision based on previous experience with systems using ORM solutions.

I've been looking at and trying to use LINQ to SQL for a while and thought I would post my findings thoughts. Before starting this series of posts I thought I would see what others have said. I had heard something about an announcement about LINQ to SQL and LINQ to Entities and found the post hear. http://blogs.msdn.com/adonet/archive/2008/10/31/clarifying-the-message-on-l2s-futures.aspx

I made a conscious decision to look at L2S rather that L2E for these reasons,

1. Its simpler

2. It allows separation of code and database mapping easily. This is commonly referred to as POCO

3. It supports more SQL features

However it seems that L2E will be enhanced to include the L2S features that are missing.

Thats a real shame and the biggest reason for me is the number of levels of interpretation.

When you right a LINQ query with L2S your LINQ query is translated to an expression tree and then translated to SQL.

With L2E you have an extra step because the entity framework is a framework that sits on stop of any provider (that has been written). To enable that it has its own data access language Entity SQL. So to get to SQL, your LINQ query is translated from LINQ to ESQL to SQL.

Anyone thats worked or seen translated text will know that often what you put in is often not what you get out. There is a common games on radio in the UK where they translate an album/track name to a language and then back to English. The quiz is then to guess what the original album/track name was given the final translated text. (A bit like Chinese whispers).

We all know how complex SQL is, so you can understand why generally the SQL you get from L2E is more complex than with L2S.

I personally like to keep things simple and so I’m disappointed by the decision to concentrate. Although all is not lost.

If you use standard practice of implementing a data layer then you could move to L2E in the future. Whats more your SELECT LINQ queries against a set of L2E objects should be similar to those for L2S. The main difference is when you want to do your insert, update and delete operations, the two technologies use different mechanisms. Further more you will likely have to change you validation code to use the different technology, although the core rules code shouldn’t have to change.

I would still stick with L2S until the next version of EF. I'll discuss some of my findings in future posts.

LINQ to SQL - Optimistic locking

simonsabin — Mon, 21 Jan 2008 20:55:29 GMT

Venturing into the world of LINQ I'm determined to give it a go because writing 3 -10 sps per table is just a pain in the bottom.

If you come from a database background one of the things that will concern you is the SQL that LINQ generates. The good news is that you can capture all the SQL that LINQ generates by using the Log property of you data context. The following sets the PubsDataContext to log to the file c:\log.txt

PubsDataContext dc = new PubsDataContext();

TextWriter tw = new StreamWriter("c:\\log.txt");

dc.Log = tw;

What I noticed straight away was updates, by default when an update is performed, irrespective of what columns are changed, all the columns are specified in the WHERE clause. This is used to enforce optimistice locking. This is to ensure that when you update data it hasn't already been updated by someone else. The downside however is that every column has to be checked.

You can change this behaviour on each column in the LINQ to SQL model that is generated by adding the UpdateCheck attribute i.e.

[Column(Storage="_col2", DbType="Int", UpdateCheck=UpdateCheck.Never)]

public System.Nullable<int> col2

{

get...

However its a bit of a pain to do that on each column in each table. However there is light at the end of the tunnel. If you have a timestamp on the table only this column will be check to verify the record has't changed.

You might be still asking why I want this at all. Imagine your bank account (£100 balance), you've cash a cheque £50 and you use the cash machine to withdraw £100, what if it happens that the bank pays the cheque into your account at the same time you use the cash point, both read the balance at £100, the chequed is cashed updating it to £150, but then the cash point withdrawl updates the £100 balance with the £100 withdrawl leaving you with a balance of £0. What happened to the cheque?

If the each transaction checked that the record had not been updated then it would have realised that the data had changed and the update would have failed.

The other option is pessimistic locking, which puts a lock on a record for the duration that the record is being read until it is updated. This is not very good for concurrency as locks cause blocking and so the number of users you application will be able to support will not be very high.