Mike Taulty's Blog : SQLServer

13th May – Session at the SQL Servers User’s Group at Microsoft, Reading

mtaulty — Tue, 12 May 2009 09:08:56 GMT

Just a quick plug – tomorrow evening I’m doing a session on ADO.NET Data Services (“Astoria”) at the SQL Server User’s Group at Microsoft, Reading.

See here for details;

http://sqlserverfaq.com/events/167/Sessions-on-Understanding-ADONET-Data-Services-Windows-7-features-indepth-and-the-usual-SQL-Nuggets-and-Networking.aspx

it’d be great to see you there.

Virtual Earth & SQL Server Event – London, April 16th

mtaulty — Wed, 01 Apr 2009 10:55:18 GMT

Johannes Kerbeck and Simon Sabin are running an event around Virtual Earth in the Microsoft offices in London on April 16th.

Sounds like an interesting take on things to me is that it seems to be angled more around taking spatial data and applying it in both places.

More details from here.

You won’t see me there though. I’ll be here.

ADO.NET Data Services Session at SQL Bits Manchester

mtaulty — Sat, 28 Mar 2009 13:05:39 GMT

A big thanks to all the people who came along to my session at SQLBits today on ADO.NET Data Services and also to the organisers and community around SQLBits that made the event happen in the first place. I was there for most of the afternoon and it seemed like a fantastic event to me in a cool venue.

As promised, my slides from the session are here for download – you’ll perhaps notice that there are a few additional (hidden) slides in the slide-deck that I didn’t use in this talk as I collapsed this talk down from an earlier talk and updated it to reflect the RTM version of Data Services and also the various CTPs/Previews that are currently kicking around.

So…you get a little more than I actually used at the conference.

Enjoy :-)

Astoria Online/Offline

mtaulty — Thu, 23 Oct 2008 13:37:39 GMT

Tantalising post from the Astoria team.

DataGrid - Master/Details

mtaulty — Wed, 01 Oct 2008 08:38:02 GMT

Following on from that previous post about the WPF DataGrid I thought it might be interesting to try and move towards more of a master/details view with a separate grid displaying the orders for each customer.

I modified the UI a little bit;

<Window
  x:Class="BlogPost.Window1"
  xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
  xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
  Title="Window1"
  Height="600"
  Width="800"
  xmlns:grid="http://schemas.microsoft.com/wpf/2008/toolkit">
  <Grid>
    <Grid.Resources>
      <Style
        x:Key="myStyle"
        TargetType="{x:Type Control}">
        <Setter
          Property="Margin"
          Value="10" />
        <Setter
          Property="FontSize"
          Value="16" />
      </Style>
    </Grid.Resources>
    <Grid.RowDefinitions>
      <RowDefinition Height="Auto"/>
      <RowDefinition
        Height="2*" />
      <RowDefinition
        Height="2*" />
      <RowDefinition Height="Auto"/>
    </Grid.RowDefinitions>
    <TextBox
      x:Name="txtCountry"
      Style="{StaticResource myStyle}"      
      Text="{Binding Country}" />
    <grid:DataGrid
      x:Name="customersGrid"
      Grid.Row="1"
      Style="{StaticResource myStyle}"
      AutoGenerateColumns="True"
      ItemsSource="{Binding}" 
      InitializingNewItem="OnCustomersGridRowInsert"/>
    <GridSplitter
      Grid.Row="2" HorizontalAlignment="Stretch"/>
    <grid:DataGrid
      x:Name="ordersGrid"
      Grid.Row="2"
      Style="{StaticResource myStyle}"
      AutoGenerateColumns="True"
      ItemsSource="{Binding ElementName=customersGrid,Path=SelectedValue.Orders}"/>
    <Button
      Grid.Row="3"
      Style="{StaticResource myStyle}"
      Content="Submit Changes"
      Click="OnSubmitChanges" />
  </Grid>
</Window>

There's something really simple and really smart going on in the UI. Notice that the second grid ( ordersGrid ) is simply declaratively binding its ItemsSource to the property SelectedValue.Orders of the customersGrid. Pretty smart.

I also updated the code behind a little bit ( I think I removed one or two functions to make it smaller ) to end up with;

  public partial class Window1 : Window, INotifyPropertyChanged
  {
    public Window1()
    {
      InitializeComponent();

      this.Loaded += OnLoaded;
    }
    void OnLoaded(object sender, RoutedEventArgs e)
    {
      // Note, letting the orders lazily load.
      dataContext = new NorthwindDataContext();

      txtCountry.DataContext = this;
    }
    public string Country 
    {
      get
      {
        return (country);
      }
      set
      {
        country = value;
        Requery();
        FirePropertyChanged("Country");
      }
    }
    void Requery()
    {
      customersGrid.DataContext = dataContext.Customers.Where(c => c.Country == txtCountry.Text);
    }
    void OnCustomersGridRowInsert(object sender, InitializingNewItemEventArgs e)
    {
      Customer c = e.NewItem as Customer;

      if (c != null)
      {
        c.Country = txtCountry.Text;
      }
    }
    void FirePropertyChanged(string property)
    {
      if (PropertyChanged != null)
      {
        PropertyChanged(this, new PropertyChangedEventArgs(property));
      }
    }
    void OnSubmitChanges(object sender, EventArgs args)
    {
      // This needs error handling in the real world.
      dataContext.SubmitChanges();
    }
    NorthwindDataContext dataContext;
    string country;

    public event PropertyChangedEventHandler PropertyChanged;
  }

That gives me a working UI in that I can display data;

and also, within reason, I can update data. What I mean by "within reason" is that I'm not sure how well this would respond to me changing (e.g.) a foreign key value but then it's perhaps pretty dubious for me to be displaying that to the user in the first place.

Note that I'm quite happily letting LINQ to SQL do lazy loading on the Orders property of the Customer entity rather than pre-loading all of those orders for the customers.

I can also go ahead and insert new customers and orders for those customers into this grid - works pretty well in that I can go and create a new customer on that top grid and then add an order to it on that bottom grid by just filling in some value ( the freight in this case );

and then submit my changes and that works exactly as you'd expect. The interesting thing here is that LINQ to SQL goes ahead and requeries for the OrderID of the inserted order and then the change notification fires and so my new order ID automatically appears on the screen. However, the new CustomerID doesn't appear on there which feels a little strange.

However, if I navigate to another order ( even before calling SubmitChanges ) and then back again the CustomerID is correctly set as below;

So what's happening there? When we call submit changes we go an do an insert into the Orders table in the database and then LINQ to SQL will requery for the identity column ( orderId ) by using scope_identity(). It then sets this on my new order object which causes a property change notification to be fired into the grid which works fine.

But this is not how the CustomerID is being set on that order. The CustomerID is never set via the property setter. It's set much earlier before we get anywhere near to SubmitChanges. What happens is that the Customer is set on the Order and the generated code goes and sets the CustomerID field but not the property meaning that you don't get property change notification so the grid doesn't update at the time that the CustomerID gets set.

What I ended up doing was to modify my code a little so that it picks up any inserted order from the DataGrid InitializingNewItem event and it syncs up to any change on the Customer on that newly inserted item and when it detects a change to the Customer it forces the Order to also send a CustomerID property change notification.

That meant modifying the definition of my ordersGrid;

    <grid:DataGrid
      x:Name="ordersGrid"
      Grid.Row="2"
      Style="{StaticResource myStyle}"
      AutoGenerateColumns="True"
      DataContext="{Binding ElementName=customersGrid,Path=SelectedValue.Orders}"
      ItemsSource="{Binding}"
      InitializingNewItem="OnOrdersGridRowInsert"/>

and then modifying my code behind a little in a few places. I modified the Order class by adding a partial method which will allow me to call its (protected) SendPropertyChanged method;

  public partial class Order : INotifyPropertyChanging, INotifyPropertyChanged
  {
    public void ForcePropertyChanged(string property)
    {
      SendPropertyChanged(property);
    }
  }

and I added a couple of more pieces to my code behind class;

    void OnOrdersGridRowInsert(object sender, InitializingNewItemEventArgs e)
    {
      Order o = e.NewItem as Order;

      if (o != null)
      {
        o.PropertyChanged += OnOrderPropertyChanged;
      }
    }
    void OnOrderPropertyChanged(object sender, PropertyChangedEventArgs e)
    {
      if (e.PropertyName == "Customer")
      {
        Order o = sender as Order;

        if (o != null)
        {
          o.ForcePropertyChanged("CustomerID");
          o.PropertyChanged -= OnOrderPropertyChanged;
        }
      }
    }

and now it's all working the way I'd expect with respect to Inserts. I'm not at all sure that this is a "good" way of achieving this, though.

What about deletes? I can select a bunch of orders and delete them and that seems to work just fine and if I delete orders and their corresponding customer then that works as well.

Where it fails ( as you might expect ) is if I delete a customer without doing anything with the related orders. If I want to make that work then I can modify the code a little. I did this by modifying the UI code - didn't find it quite as easy as I would have liked in that I seem to have to go down to the route of grabbing an ICollectionView from the customersGrid and then waiting for its CollectionChanged event whereas I was perhaps expecting the DataGrid to have some kind of Deleted event. Here's the code I ended up with;

  public partial class Window1 : Window, INotifyPropertyChanged
  {
    public Window1()
    {
      InitializeComponent();

      this.Loaded += OnLoaded;
    }
    public string Country 
    {
      get
      {
        return (country);
      }
      set
      {
        country = value;
        Requery();
        FirePropertyChanged("Country");
      }
    }
    void OnLoaded(object sender, RoutedEventArgs e)
    {
      // Note, letting the orders lazily load.
      dataContext = new NorthwindDataContext();
      txtCountry.DataContext = this;
    }
    void Requery()
    {
      customersGrid.DataContext = dataContext.Customers.Where(c => c.Country == txtCountry.Text);
      ICollectionView colView = CollectionViewSource.GetDefaultView(customersGrid.DataContext);
      colView.CollectionChanged += OnCustomerListModified;
    }
    void OnCustomerListModified(object sender, System.Collections.Specialized.NotifyCollectionChangedEventArgs e)
    {
      if (e.Action == NotifyCollectionChangedAction.Remove)
      {
        foreach (object o in e.OldItems)
        {
          Customer c = o as Customer;

          if ((c != null) && (c.Orders != null))
          {
            dataContext.Orders.DeleteAllOnSubmit(c.Orders);
          }
        }
      }
    }
    void OnCustomersGridRowInsert(object sender, InitializingNewItemEventArgs e)
    {
      Customer c = e.NewItem as Customer;

      if (c != null)
      {
        c.Country = txtCountry.Text;
      }
    }
    void OnOrdersGridRowInsert(object sender, InitializingNewItemEventArgs e)
    {
      Order o = e.NewItem as Order;

      if (o != null)
      {
        o.PropertyChanged += OnOrderPropertyChanged;
      }
    }
    void OnOrderPropertyChanged(object sender, PropertyChangedEventArgs e)
    {
      if (e.PropertyName == "Customer")
      {
        Order o = sender as Order;

        if (o != null)
        {
          o.ForcePropertyChanged("CustomerID");
          o.PropertyChanged -= OnOrderPropertyChanged;
        }
      }
    }
    void FirePropertyChanged(string property)
    {
      if (PropertyChanged != null)
      {
        PropertyChanged(this, new PropertyChangedEventArgs(property));
      }
    }
    void OnSubmitChanges(object sender, EventArgs args)
    {
      // This needs error handling in the real world.
      dataContext.SubmitChanges();      
    }
    NorthwindDataContext dataContext;
    string country;

    public event PropertyChangedEventHandler PropertyChanged;
  }

and, in fact, here's the whole project for download should you want the source code.

Messing with Dynamic LINQ Queries

mtaulty — Thu, 11 Sep 2008 08:08:13 GMT

Mike was chatting to me about how you'd take something like this ( against LINQ to SQL and the Northwind database );

 using (NorthwindDataContext ctx = new NorthwindDataContext() { Log = Console.Out })
      {
        string[] values = { "A", "B", "C" };

        var query =
          from c in ctx.Customers
          select c;

        foreach (string s in values)
        {
          string t = s; // Care to avoid capturing the same value 3 times...
          query = query.Where(c => c.CompanyName.Contains(t));
        }
        query.ToList();  
      }
      Console.ReadLine();

which produces;

SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactT
itle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Coun
try], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
WHERE ([t0].[CompanyName] LIKE @p0) AND ([t0].[CompanyName] LIKE @p1) AND ([t0].
[CompanyName] LIKE @p2)
-- @p0: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [%C%]
-- @p1: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [%B%]
-- @p2: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [%A%]

and write a new extension method Where which could take the list of arguments ("A", "B", "C") and a single Lambda and just "figure it out".

I spent ages on this writing reams of code but in the end I think I chopped it down to;

  public static class QueryExtensions
  {
    public static IQueryable<T> Where<T,S>(this IQueryable<T> query, 
      Expression<Func<T,S,bool>> func, params S[] args)
    {      
      foreach (S s in args)
      {
        MethodCallExpression mce = func.Body as MethodCallExpression;
        Expression e = MethodCallExpression.Call(mce.Object, mce.Method, Expression.Constant(s));
        query = query.Where<T>(Expression.Lambda<Func<T, bool>>(e, func.Parameters[0]));
      }
      return (query);
    }
  }

and all I'm really attempting to do there is to take the Expression which will have 2 parameters and reduce it down to an Expression which has 1 parameter by hard-wiring the 2nd parameter with the current value of the loop variable "s" into the method call expression itself as a constant. Note sure if that's quite right but I can then use it as;

      using (NorthwindDataContext ctx = new NorthwindDataContext() { Log = Console.Out })
      {
        var query =
          from c in ctx.Customers.Where( (c, s) => c.CompanyName.Contains(s), "A", "B", "C")
          select c;

        query.ToList();  
      }
      Console.ReadLine();

which seems to produce;

SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactT
itle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Coun
try], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
WHERE ([t0].[CompanyName] LIKE @p0) AND ([t0].[CompanyName] LIKE @p1) AND ([t0].
[CompanyName] LIKE @p2)
-- @p0: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [%C%]
-- @p1: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [%B%]
-- @p2: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [%A%]

which looks alright. I'm not sure that it's universally useful in that Where is basically AndWhere so whilst Contains might work here there perhaps aren't that many other methods that would make sense.

Regardless, it took me quite a while and I still don't really understand building dynamic expressions at all properly :-)

Some older bits about dynamic queries and the such like;

http://mtaulty.com/CommunityServer/blogs/mike_taultys_blog/archive/2007/11/10/9911.aspx
http://mtaulty.com/CommunityServer/blogs/mike_taultys_blog/archive/2007/06/15/9489.aspx
http://mtaulty.com/CommunityServer/blogs/mike_taultys_blog/archive/2007/06/18/9490.aspx
http://mtaulty.com/CommunityServer/blogs/mike_taultys_blog/archive/2007/06/18/9492.aspx

SQL 2008, Filestream, Docs from VB

mtaulty — Mon, 08 Sep 2008 06:56:45 GMT

Someone asked me if I had a simple sample of how to read/write documents into a database table using FILESTREAM in SQL Server 2008.

I set about it.

Firstly, I had to find out how to switch FILESTREAM on in the RTM of SQL Server. At times like this you need Bob Beauchemin to tell you how it's done and then you can go and run code such as this once you've done the necessary bits in SQL Configuration manager;

exec sp_configure filestream_access_level, 2
reconfigure
go

CREATE DATABASE Documents 
ON
PRIMARY 
( 
    NAME = DocData,
    FILENAME = 'c:\temp\docs.mdf'
),
FILEGROUP FileGroup CONTAINS FILESTREAM
( 
    NAME = DocFiles,
    FILENAME = 'c:\temp\docstreams'
)
LOG ON  
( 
    NAME = DocLog,
    FILENAME = 'c:\temp\docs.ldf'
)
GO

and now I've got a database that can accept file stream data. Phew. Time for a simple table;

create table Docs
(
    id uniqueidentifier rowguidcol not null primary key,
    documentData varbinary(max) filestream not null,
    documentName nvarchar(1024) not null
)

Now I've got a table I can use something like Windows Presentation Foundation with VB to build a simple UI to allow me to add documents to the database and then edit them. Note that editing them here just means sucking out the file data, writing it to a temporary file, passing that to the Shell for editing and then saving the contents back to the database.

There ended up being too much code to paste in here as a blow-by-blow account but here's my rather grey looking UI;

with a few items from my desktop that I've added to my database table.

The code's pretty hacky with almost no error handling but I thought I'd share it here for download regardless.

On LINQ to SQL, Concurrency and Timestamps

mtaulty — Tue, 01 Jul 2008 14:10:31 GMT

I came across a bit of a glitch in using timestamps for checking concurrency violations in LINQ to SQL and thought I'd share.

Say I've got a table like;

create table Person
(
  id int identity primary key,
  firstName nvarchar(30),
  lastName nvarchar(30),
  timestamp 
)

so, we have a simple table that has a timestamp on it and I want to use that to detect any concurrency problems that I might have in LINQ to SQL.

Let's populate this table with some data;

insert person(firstname,lastname) values('first1', 'last1')
insert person(firstname,lastname) values('first2', 'last2')
insert person(firstname,lastname) values('first3', 'last3')

and then I can bring that into my LINQ to SQL environment and I can check what the concurrency options are set to;

So, you can see that we've got Update Check set to Always and that means that the timestamp will be added to any where clauses for Deletes or Updates and if a particular update doesn't find any row to update because of that where clause then we have a concurrency violation.

Note also that Auto-Sync is set to Always which is a good thing because it means that if we insert an entity we'll automatically get the timestamp back and if we update an entity we'll similarly get an updated timestamp so that the timestamp in the original values of our entity in memory will represent the timestamp of the record when we queried/inserted/updated it and will only get "stale" if someone else were to update it (or delete it) in the meantime.

So, with LINQ to SQL we can write some code such as the stuff below which prompts for a new surname, changes the value in the entities that it has queried then tries to do a SubmitChanges and tries to make sure that it captures as many concurrency violations as it can ( the ContinueOnConflict parameter ).

    using (DemoDataContext ctx = new DemoDataContext())
    {
      var people = ctx.Persons.ToList();

      Console.WriteLine("Enter a new surname");
      string surname = Console.ReadLine();

      // Cause some changes.
      foreach (Person p in people)
      {
        p.lastName = surname;
      }
      // HERE...
      Console.WriteLine("Now's the time to cause a concurrency violation");
      Console.ReadLine();

      bool allSaved = false;

      while (!allSaved)
      {
        try
        {
          ctx.SubmitChanges(ConflictMode.ContinueOnConflict);
          allSaved = true;
        }
        catch (ChangeConflictException ex)
        {
          Console.WriteLine("Hit a conflict - fixing it!");

          foreach (ObjectChangeConflict conflict in ctx.ChangeConflicts)
          {
            conflict.Resolve(RefreshMode.KeepChanges);
          }
        }
        catch (Exception ex)
        {
          Console.WriteLine("Hit an unexpected exception [{0}]",
            ex.Message);
        }
      }
    }

When we hit the line of code marked HERE above, I go and run this piece of T-SQL;

update person set lastName='anything' where id=2
update person set lastName='else' where id=3

to cause concurrency problems for row number 2 and number 3 in my database.

What should happen;

We read 3 entities with timestamps T1, T2, T3.
We modify the surnames in our code.
We use T-SQL to modify the timestamps T2, T3 in the database to ( say ) TM2 and TM3.
We submit our changes.
Our modifications for entities 2 and 3 will fail because T2!=TM2 and T3!=TM3. Our transaction is rolled back.
We use Resolve in order to keep any changes that we have made in our code ( i.e. the surname modification ) but to refresh the other entity values from the DB.
We now have in our program entities with timestamps T1, TM2, TM3
We submit our changes.
The code completes.

However, that's not what happens when the code does. What happens is that we hit an unexpected exception at 8 above and my Console.WriteLine writes it out;

Hit an unexpected exception [Value of member 'timestamp' of an object of type 'Person' changed.

What's going on here? I think what's happening here is that when we hit the call to SubmitChanges we go to the database to update 3 entities. The first update works so we update the timestamp. The 2nd update fails and so does the 3rd so we rollback the transaction.

However, we've updated the timestamp on that 1st entity.

We then go and resolve the 2 concurrency violations we've got before calling SubmitChanges again. It takes a look at that first entity which didn't have a concurrency problem but does now have a modified timestamp and still needs updating in the database ( because the transaction was rolled back ) and says "Hey, you're not meant to change generated columns like this!!".

Got it?

Option - Stop LINQ to SQL from Updating the TimeStamp Automatically on Update

The timestamp is only being updated because we have allowed the Auto-Sync property to be default to Always in the mapping information. That means "re-sync this column whenever we insert, update".

We could change this so that we only do Auto-Sync on Insert by setting;

Now, our code as it originally stands is going to work just fine if I re-run it under the same conditions where I cause a concurrency violation at the line marked HERE because when we hit the initial call to SubmitChanges which throws a ChangeConflictException we catch that exception, call Resolve on the 2 rows with the problems and then we call SubmitChanges again and we've never updated the timestamp on row number 1.

However...this isn't a free ride.

The problem with switching Auto-Sync to OnInsert means that whenever we actually do a successful update we won't update the timestamp to reflect the latest value that we just generated in the database.

So, whenever we call SubmitChanges for subsequent updates we will see superfluous concurrency exceptions for every row that didn't hit a concurrency exception the last time we called SubmitChanges.

That is, this code (which runs forever) will work;

    using (DemoDataContext ctx = new DemoDataContext())
    {
      var people = ctx.Persons.ToList();

      while (true)
      {
        Console.WriteLine("Enter a new surname");
        string surname = Console.ReadLine();

        // Cause some changes.
        foreach (Person p in people)
        {
          p.lastName = surname;
        }
        // HERE...
        Console.WriteLine("Now's the time to cause a concurrency violation");
        Console.ReadLine();

        bool allSaved = false;

        while (!allSaved)
        {
          try
          {
            ctx.SubmitChanges(ConflictMode.ContinueOnConflict);
            Console.WriteLine("Submitted changes");
            allSaved = true;
          }
          catch (ChangeConflictException ex)
          {
            Console.WriteLine("Hit a conflict - fixing it!");

            foreach (ObjectChangeConflict conflict in ctx.ChangeConflicts)
            {
              conflict.Resolve(RefreshMode.KeepChanges);
            }
          }
          catch (Exception ex)
          {
            Console.WriteLine("Hit an unexpected exception [{0}]",
              ex.Message);
          }
        }
      }
    }

and it'll work regardless of whether I cause concurrency problems at the line marked HERE. However, once the loop has executed once we run the risk of hitting "false" concurrency problems at the call to SubmitChanges for any rows that have out of date timestamps because we did not update them the last time we did an update.

The other thing that comes to mind here is that this all depends on the lifetime of your DataContext. If you just use it for one "unit of work" then this isn't really going to bite you in the same way because Auto-Sync doesn't seem so relevant if you're going to throw your DataContext away after you call SubmitChanges anyway.

And the last thing is that this won't affect you at all unless you're using generated columns like timestamps for concurrency checking.

Michael Rys in London on Non-Relational Data in SQL Server

mtaulty — Wed, 25 Jun 2008 06:51:36 GMT

The UK, SQL Server User Group has Michael Rys delivering a talk on Monday in London. Michael's a Program Manager in SQL Server.

Go here for the details and to sign up.

I don't know Michael personally but I know that back when I was looking at SQL Server 2005 and I asked any kind of question about the new XML data type it was more-often-than-not an email that came back from Michael with the answer in it so you should be in for a treat if you manage to get yourself signed up for this.

"When Do Queries Execute"

mtaulty — Wed, 21 May 2008 06:18:17 GMT

Julie's got a great post about when stored procedures execute when using LINQ to Entities.

It made me think about LINQ to SQL where if I've got something like;

      using (NorthwindDataContext ctx = new NorthwindDataContext())
      {
        var query = ctx.GetCustomersByCountry("UK"); // SQL Happens Here!

        foreach (Customer c in query)
        {
          Console.WriteLine(c.CustomerID );
        }
      }

Then we see the same effect as Julie's seeing in LINQ to Entities (note GetCustomersByCountry is a stored procedure) whereas if I've got something like;

     using (NorthwindDataContext ctx = new NorthwindDataContext())
      {
        var query = ctx.FnGetCustomersByCountry("UK"); 

        foreach (Customer c in query) // SQL Happens Here
        {
          Console.WriteLine(c.CustomerID );
        }
      }

where FnGetCustomersByCountry is a table-valued function then the query is executed at a different point in time.

I guess one reason for this is that table-valued functions are composable in LINQ to SQL so I can do something like;

      using (NorthwindDataContext ctx = new NorthwindDataContext())
      {
        var query = ctx.FnGetCustomersByCountry("UK");

        query =
          from c in query
          where c.CustomerID.StartsWith("B")
          select c;

        foreach (Customer c in query) // SQL Happens Here
        {
          Console.WriteLine(c.CustomerID );
        }
      }

whereas I can't do that with a stored procedure so maybe it makes sense to execute the stored procedure ASAP as deferring the execution would add no value but ( on the other hand and as Julie says ) it's a bit confusing to the developer who's got used to queries occurring when we enumerate.

For the table-valued-function case, not to defer the query would be a problem because we'd then be applying the StartsWith clause in memory on the client-side rather than adding it to the query on the server-side.

If you look at the generated code on the DataContext you see;

    [Function(Name="dbo.FnGetCustomersByCountry", IsComposable=true)]
    public IQueryable<Customer> FnGetCustomersByCountry([Parameter(DbType="NVarChar(MAX)")] string country)
    {
      return this.CreateMethodCallQuery<Customer>(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), country);
    }
    
    [Function(Name="dbo.GetCustomersByCountry")]
    public ISingleResult<Customer> GetCustomersByCountry([Parameter(DbType="NVarChar(MAX)")] string country)
    {
      IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), country);
      return ((ISingleResult<Customer>)(result.ReturnValue));
    }

so on the one hand we have CreateMethodCallQuery and on the other we have ExecuteMethodCall and, just to be devious, I wondered if I could do a cheap hack here and try and get deferred execution of my stored procedure just by trying to use CreateMethodCallQuery but I didn't manage to get that going yet - clearly, there'd be other ways of achieving the same thing.

Finally, there's also the question of when the data reader is actually read from the wire when you're doing this kind of work which I talked about a little here.

Mocking LINQ to SQL

mtaulty — Fri, 09 May 2008 07:21:13 GMT

Matt Warren has an "interesting" post on how you could hack your way to a mocked implementation of LINQ to SQL - if I had a pound for every time someone's asked me how to do this then I'd have at least 3 pounds :-)

Seriously though, there is interest in doing this kind of thing and it's a good starting place if you're inclined to head down this route.

LINQ to SQL and SqlConnection ( "when you're wrong, you're wrong" :-) )

mtaulty — Thu, 20 Mar 2008 08:56:10 GMT

I was talking to Ian and another chap at the UK Launch yesterday about LINQ to SQL and my use of the DataContext in some demo code where I always tend to write something like;

      using (DataContext ctx = new DataContext())
      {
      }

and whether I actually needed to do that dispose or not?

Now...I was pretty confident that I do need to do this for two reasons;

DataContext is disposable so I figure I should probably Dispose() of it - generally been my approach to anything IDisposable.
Someone had recently asked me whether DataContext.Dispose() actually did close the SQL connection and I'd done a little bit of reflecting on it and spotted that;

DataContext.Dispose() -> SqlProvider.Dispose() -> SqlConnectionManager.DisposeConnection() -> SqlConnection.Close()

although the code paths are more complex than that and I haven't attempted to figure out under what conditions the various calls happen.

So, I'd kind of reasoned that;

If you don't dispose of the DataContext then you won't get the connection closed and back in the pool until finalisation time which is generally a bad idea.

but then I started to wonder whether that assumption was right and it turns out that it isn't really right but then it's not exactly wrong either :-)

So, to test this all out I did a bit of debugging and wrote;

 using (NorthwindDataContext ctx = new NorthwindDataContext())
      {
        (from c in ctx.Customers select c).ToList();

        (from c in ctx.Customers select c).ToList();

        (from c in ctx.Customers select c).ToList();
      }

and ran it under the debugger with breakpoints in SqlConnection.Open() and SqlConnection.Close().

I was foxed for a while because I see 5 calls to Open() and 5 calls to Close(). I was expecting either;

1 call to Open and 1 to Close.
3 calls to Open and 3 to Close.

A quick look at the call-stacks showed that it seems like when we hit the first query to be evaluated we first do;

which looks like we're opening the connection in order to test if it's SQLCE or not. Then we see a Close followed by;

So...a similar check to see if it's SQL 2000 or earlier followed by a Close.

So...following that I see an Open/Close pairing around each query that I execute which suggests that, in an ideal world, there'd be no need to dispose of the context because the connection is being opened/closed on demand every time we do a query. The Close calls that I see are coming from;

That is - they are not coming from Dispose on the DataContext but, instead, they are coming from the Dispose on the ObjectReaderCompiler.ObjectReader. Who he?

Well, it looks like he's the IEnumerator that's returned for my foreach iteration and he's IDisposable so if he gets disposed (i.e. when you've read all the data) then he closes the connection. He's getting disposed here because foreach is being friendly but it's not hard to come up with a situation where that might not happen;

So...I should be able to defeat that with something that leaves the enumerator kicking around like;

 using (NorthwindDataContext ctx = new NorthwindDataContext())
      {
        var query = from c in ctx.Customers
                    select c;

        var enumerator = query.GetEnumerator();
      }

and I finally see the DataContext stepping in and disposing and ultimately closing the connection;

Now, arguably I should be disposing of the enumerator here and that's solve the problem of the open connection again but really that just goes back to the original idea which is "if it's disposable, dispose it" so I've really just moved the decision from one disposable type to another.

Additionally, I'd imagine that there are lots of other places where not disposing the DataContext could mean that you wait until a finalizer runs in order to get a connection back into the pool so it feels like the right thing to do is always to Dispose() to be safe rather than sorry.

There's a FAQ up here about LINQ to SQL;

http://msdn2.microsoft.com/en-us/library/bb386929.aspx

which has a section in it about the lifetime of connections so that'd be the right place to go for a definitive answer.

I like simple things so I'm going to stick with my basic law of "If it's disposable, dispose of it" :-)

LINQ to SQL, Stored Procs, Output Params, Readers Reading

mtaulty — Thu, 13 Mar 2008 15:31:43 GMT

I had a discussion at DevWeek about when SqlDataReader.Read() gets called for resultsets coming back from stored procedures accessed with LINQ to SQL.

I've got this stored proc;

create procedure dbo.GetCustomersByCountry  
 @country nvarchar(max)  
as  
 set nocount on  
 select * from dbo.customers  
 where country = @country

and I've brought it into LINQ to SQL using the designer and that means that I can write code such as;

 using (NorthwindModelDataContext ctx =
      new NorthwindModelDataContext())
    {
      var query = ctx.GetCustomersByCountry("UK");

      foreach (Customer c in query)
      {
        Console.WriteLine(c.CustomerID);
      }
    }

Question is - when does SqlDataReader.Read() get called here? Do we read all of the data up front and then just provide access to an in-memory collection or is the data read from the wire as the loop iterates over it?

I wanted to check so I went ahead and set some breakpoints in a few places;

Inside the constructor of my Customer class.
Inside SqlDataReader.Read()

and what I see there is an interleaved set of calls which go [Read->Construct->Read->Construct] and so the results are being returned as they are read from the wire - i.e. as the foreach loop pulls them out object by object.

Now, here's another stored procedure;

create procedure TestOutputProc(  
 @id int output  
)  
as  
 set nocount on  
 select * from dbo.customers  
 set @id = 10

This has an output parameter. I bring this into the LINQ to SQL environment and I program against it like this;

  using (NorthwindModelDataContext ctx =
      new NorthwindModelDataContext())
    {
      int? x = null;

      var query = ctx.TestOutputProc(ref x);

      Console.WriteLine(x);

      foreach (Customer c in query)
      {
        Console.WriteLine(c.CustomerID);
      }
    }

Notice - the output parameter is available before I've started to enumerate through the query results. If I go and run this under my debugger then what I see is [Read->Read->Read->Read-> ....] and so the entire resultset is read before we get to the Console.Writeline.

This isn't entirely unexpected - AFAIK you need to read the resultset before you can get to the output parameter and that's what is surfacing inside of LINQ to SQL here. However, it does mean that you need to apply some thought if you're going to return a lot of data from a procedure with an outgoing parameter.

ADO.NET Entity Framework - QueryViews, Inline Functions in SSDL

mtaulty — Fri, 08 Feb 2008 17:00:25 GMT

I have a mail from Tim on the EF team that I've had for a little while. I'd asked a question about mapping and he'd given me an answer saying something like "Oh, you could always do X, Y, Z" and I'd left this mail in my inbox for a while because I've been distracted and also because I didn't really understand how to do X, Y and Z but I didn't really want to say :-)

Today, I think I understood a bit more of it and the result is twofold.

The first of those folds is that I'm thinking that I'd really like to spend maybe a week in a darkened room doing nothing but Entity Framework but I doubt that I'll get the chance as (strangely) in my job I'm not really encouraged to learn technology but that's another story and not one that I'll bore you with here.

The second of those folds is that I really think that the Entity Framework still has a whole lot of documentation and explanation that's going to be needed if people are really going to get it - there's a lot in there and it's not immediately obvious how to use it and (frankly) right now I find that sitting in an XML editor with an EDMX file, IntelliSense, the docs and a whole tonne of curiosity is proving to be far more rewarding than sitting in the designer. Don't get me wrong, the designer's fine and I'm sure it'll get better but, right now, it seems to limit choices making you think that you can only do a few things when you can actually do a lot more.

An aside...if you're putting together some kind of data-oriented conference feel free to include many sessions on Entity Framework. I feel that you need at least;

Overview of Entity Framework and LINQ to Entities.

One or possibly two sessions just to explain what SSDL, CSDL and MSL can actually do for you. It needs to be deep stuff with lots of examples.

Session explaining EntitySQL and what additional constructs it has (I wrote about some of those once and gathered up the links here).

Session explaining LINQ to Entities.

Possibly a session explaining how to use the designer, how to use the command line tools and what the top 10 error messages mean :-)

Sure, it's not every conference that's going to be able to include all that stuff but someone, somewhere needs to do it :-)

Anyway, what I hadn't appreciated previously that Tim had alluded to in his mail was that I can have a piece of SSDL that looks like this (deliberately kept simple);

      <Schema Namespace="NorthwindModel.Store"
              Alias="Self"
              ProviderManifestToken="09.00.3054"
              xmlns="http://schemas.microsoft.com/ado/2006/04/edm/ssdl">
        <EntityContainer Name="dbo">
          <EntitySet Name="Customers" EntityType="NorthwindModel.Store.Customers" />
        </EntityContainer>
        <EntityType Name="Customers">
          <Key>
            <PropertyRef Name="CustomerID" />
          </Key>
          <Property Name="CustomerID" Type="nchar" Nullable="false" MaxLength="5" />
          <Property Name="CompanyName" Type="nvarchar" Nullable="false" MaxLength="40" />
          <Property Name="ContactName" Type="nvarchar" MaxLength="30" />
          <Property Name="ContactTitle" Type="nvarchar" MaxLength="30" />
          <Property Name="Address" Type="nvarchar" MaxLength="60" />
          <Property Name="City" Type="nvarchar" MaxLength="15" />
          <Property Name="Region" Type="nvarchar" MaxLength="15" />
          <Property Name="PostalCode" Type="nvarchar" MaxLength="10" />
          <Property Name="Country" Type="nvarchar" MaxLength="15" />
          <Property Name="Phone" Type="nvarchar" MaxLength="24" />
          <Property Name="Fax" Type="nvarchar" MaxLength="24" />
        </EntityType>
        <Function Name="InsUkCustomer"
                  IsComposable="false">
          <CommandText>insert customers(CustomerId,CompanyName) values(@Id, @Company)</CommandText>
          <Parameter Name="Id"
                     Mode="In"
                     Type="nvarchar"/>
          <Parameter Name="Company"
                     Mode="In"
                     Type="nvarchar" />
        </Function>
        <Function Name="DelUkCustomer"
                  IsComposable="false">
          <CommandText>
            delete customers where customerid = @id
          </CommandText>
          <Parameter Name="Id"
                     Mode="In"
                     Type="nvarchar"/>            
        </Function>
        <Function Name="UpdUkCustomer"
                  IsComposable="false">
          <CommandText>
            /* TODO */
          </CommandText>
        </Function>
      </Schema>

This is just saying that we have an entity in the DB called Customers (it's from Northwind) and that we've got three functions. Except, they're not really functions. They're just pieces of SQL defined right here in the SSDL file with the <CommandText/> element. I hadn't appreciated that this was something that I could do - I figured that Functions meant SPs in the database but, clearly, that's not the case :-)

With that in place, I've defined in my conceptual model an entity set called UkCustomers which is ( as you'd expect ) just populated with customers from the UK. It looks like this;

<Schema Namespace="NorthwindModel"
              Alias="Self"
              xmlns="http://schemas.microsoft.com/ado/2006/04/edm">
        <EntityContainer Name="NorthwindEntities">
          <EntitySet Name="UkCustomers"
                     EntityType="NorthwindModel.Customers" />
        </EntityContainer>
        <EntityType Name="Customers">
          <Key>
            <PropertyRef Name="CustomerID" />
          </Key>
          <Property Name="CustomerID"
                    Type="String"
                    Nullable="false"
                    MaxLength="5"
                    FixedLength="true" />
          <Property Name="CompanyName"
                    Type="String"
                    Nullable="false"
                    MaxLength="40" />
          <Property Name="ContactName"
                    Type="String"
                    MaxLength="30" />
          <Property Name="ContactTitle"
                    Type="String"
                    MaxLength="30" />
          <Property Name="Address"
                    Type="String"
                    MaxLength="60" />
          <Property Name="City"
                    Type="String"
                    MaxLength="15" />
          <Property Name="Region"
                    Type="String"
                    MaxLength="15" />
          <Property Name="PostalCode"
                    Type="String"
                    MaxLength="10" />
          <Property Name="Country"
                    Type="String"
                    MaxLength="15" />
          <Property Name="Phone"
                    Type="String"
                    MaxLength="24" />
          <Property Name="Fax"
                    Type="String"
                    MaxLength="24" />
        </EntityType>
      </Schema>

So, nothing radical in there at all but what I find "fun" is in the MSL mapping between these two things which I did using a QueryView. Julie has a great post over here about QueryViews and DefiningQuery.

Here's my mapping;

      <Mapping Space="C-S"
               xmlns="urn:schemas-microsoft-com:windows:storage:mapping:CS">
        <EntityContainerMapping StorageEntityContainer="dbo"
                                CdmEntityContainer="NorthwindEntities">
          <EntitySetMapping Name="UkCustomers">
            <QueryView>
              using NorthwindModel;
              select value NorthwindModel.Customers(
              c.CustomerID, c.CompanyName, c.ContactName, c.ContactTitle,
              c.Address, c.City, c.Region, c.PostalCode, c.Country,
              c.Phone, c.Fax)
              from dbo.Customers as c
              where c.Country = 'UK'
            </QueryView>
            <EntityTypeMapping TypeName="NorthwindModel.Customers">
              <ModificationFunctionMapping>
                <InsertFunction FunctionName="NorthwindModel.Store.InsUkCustomer">
                  <ScalarProperty Name="CustomerID" ParameterName="Id" Version="Current"/>
                  <ScalarProperty Name="CompanyName" ParameterName="Company" Version="Current"/>
                </InsertFunction>
                <DeleteFunction FunctionName="NorthwindModel.Store.DelUkCustomer">
                  <ScalarProperty Name="CustomerID" ParameterName="Id" Version="Original"/>
                </DeleteFunction>
                <UpdateFunction FunctionName="NorthwindModel.Store.UpdUkCustomer"/>
              </ModificationFunctionMapping>
            </EntityTypeMapping>              
          </EntitySetMapping>
        </EntityContainerMapping>
      </Mapping>

I found this "interesting" :-) I can just define a query that brings back my UK customers and when it comes to doing the insert, update, delete on it I can just map those to the (incomplete) functions that I wrote inline at the store level.

Neat. Then I can go write some LINQ code;

      using (NorthwindEntities ent = new NorthwindEntities())
      {
        Customer c = new Customer()
        {
          CustomerID = "TESTX",
          CompanyName = "C1"
        };
        ent.AddToUkCustomers(c);
        ent.SaveChanges();
      }

I found this exciting enough that I'm finishing this post at 1am having been playing around with this stuff - how sad is that? :-)

SQLBits - Birmingham, 1st March

mtaulty — Wed, 30 Jan 2008 02:06:45 GMT

Just a quick plug :-)

I'm speaking at the "SQLBits" conference in Birmingham on the 1st March.

The agenda is up here.

The agenda is built by voting from prospective attendees and I found it interesting in that I submitted a few sessions that looked something like;

"Entity Framework Tour"

"LINQ to SQL" Tour

"LINQ to Entities" Tour (this is similar to the first topic but with a very different emphasis)

"Understanding LINQ"

and what surprised me is that the last session was the one that got picked despite it being the session that I'd say is least directly related to SQL - i.e. you can quite happily have some LINQ without bothering with a relational database.

So...if you can make the date and the location then sign up for SQL Bits and I look forward to seeing you at my session if it appeals to you. As an aside, if there's something that you'd like to see included in a session like "Understanding LINQ in .NET Framework V3.5 and Beyond" then feel free to ping me a mail letting me know what it is that you want to see and I'll see what I can do.