Can’t wait for C#4…

Following on from my posts on dynamic typing… this week I wrote a quick app to import some data from a load of XML files to a load of tables in a SQL database. The columns in the tables and the attributes in the xml file have a 1:1 mapping. So I firstly wrote some code which goes through a given table and reads the schema from the database and creates a C# type based on the schema (using sp_columns). Then I placed a ColumnAttribute on each column etc. etc., and then goes through the task of iterating through the XML data and creating objects of that new type, copy the data across (doing the odd data type conversion along the way), and finally use the standard LinqToSql DataContext to insert the data into the DB. But a lot of the code was somewhat ugly – messing about with TypeBuilders and FieldBuilders and the like – I’m sure that I could have done something much more elegant in C#4…. none of it was compile-time safe, it was all using reflection etc.. But it did work great anyway – apparently was used to migrate a good few thousand records of XML data into SQL relatively quickly 🙂


Scott Guthrie’s talk on LIDNUG

Finished the webcast a couple of hours ago. Great session with lots of interesting questions and answers – thanks Scott! The most important question from my point of view (and judging by the number of people that asked it, not just me – this was by far the most common one), was Scott’s take on Linq to SQL and how it sits alongside Entity Framework - and what’s the future of L2S. Whilst Scott doesn’t work directly on the ADO .NET team, he was able to largely answer this anyway: L2S is definitely a part of the future of .NET and that they have no plans to kill it off (at the moment!). Microsoft see Entity Framework as hopefully taking a number of the best bits out of L2S and incorporating them into EF (which should be included in .NET 4), but they also plan on keeping L2S going, too. He also addressed for the much-publicised blog that the ADO .NET team put out which has been largely mis-interpreted by the public – they never meant to suggest that L2S is dead. He also mentioned that they will be putting a new blog post out in the near future addressing this issue further. So, glad that’s been cleared up 🙂 For what it’s worth – I see L2S as being an excellent ORM tool for rapid application development, small applications, prototypes etc. – essentially anything where you are working with e.g. a 1-to-1 mapping between your database and your domain model – whilst you might prefer to look at EF for more complex object models. However, from what I have seen of EF (which admittedly is not a great deal) and from other blogs out there, there’s still much work to be done on it before it’s going to be adopted by the majority of the L2S crowd.

LINQ to SQL Inheritance problem

Can anyone solve this issue? Imagine you have two classes, ProjectManager and Developer. Both are of type Person (except for really spineless PMs and uber-nerd developers), and every ProjectManager has a number of Developers under their command. You correctly set the discriminator value and make Person an abstract class: You then want to create a one-to-many relationship between PM and Developer so that in code you can navigate between e.g. a ProjectManager and their Developers using something like: foreach (var Pm in Context.ProjectManagers) System.Console.WriteLine ("Project Manager Id {0} has {1} many developers", Pm.PersonId, Pm.Developers.Count()); Also bear in mind that both classes (well, all three in fact) share the same physical database table in LINQ-to-SQL’s single-table-inheritance model. So then you try to add an Association between ProjectManager and Developer, by joining between Developer.ProjectManagerPersonId and ProjectManager.PersonId: - Whoops! Where’s the PersonId column on the ProjectManager? This should be displayed in the drop-down because it’s inherited from the Person base class. But it’s not there, so you can’t add the Association between the Developer and ProjectManager. The workaround I found was to use the designer to add an Association between Person and Developer instead. The most obvious problem here is that all classes which derive from Person has a collection of Developers underneath them – even a Developer itself! This is obviously not what you want. So, I tried going into the designer.cs of the DataContext, and changing all the Association objects etc. to point to the Project Manager instead of Person. And believe it or not, it seems to work! This is not ideal though – even if you were to move the code out of the designer.cs into your partial class, it’s still a hack. So, is this a bug with Linq To Sql or just me trying something out that is going “too far”. And please don’t mention ADO .NET Entity Framework as we’re using L2S on the project that I’ve got this issue on.

More about LINQ

Some more decent LINQ videos mostly featuring Luca Bolognese, who was/ is Lead Programme Manager for the LINQ C# team. Excellent speaker. LINQ to SQL Pipeline Video with Luca Bolognese and Matt Warren Another TechEd Video (I think!) Anders Hejlsberg: The .NET Language Integrated Query (LINQ) Framework Overview I've also found out the answer (or an answer) to my question regarding LINQ to SQL security - first I thought of using Stored Procedures as a way of doing this - but that's going to be inefficient. Imagine the following scenario: You want to build some set of complex queries from Northwind. Lots of different varieties of queries, perhaps some dynamic queries. Several options: You can right a load of SPs, one for each of them, but that kind of defeats the object of LINQ in a way as you are doing all the queries in SQL as SPs. But it works, I guess. You can use a set of core SPs such as CustomerSelAll, CustomerSelByCountry etc. etc. - and from there use LINQ to generate subqueries on that data. Problem with this approach is that you end up bringing back large amounts of data back to the client and then only using a small amount of it. You can ignore SPs completely and just use LINQ directly to the database. Easiest option but no security on views of data. OR - you can use Table Value Functions. These are - as far as I can see - the same as SPs which would return a result set really except these return a result set that you can query directly e.g. SELECT * FROM MyTvf() WHERE SomeField = SomeValue You can't do this with a SP. So you can use this in conjunction with LINQ so that you can secure the function, and still do a query against that result set on SQL before returning it to the client. So if you have a TVF which returns all Customers by Country, you could something like this in LINQ: var Query = from Cust in MyDataContext.CustomerSelByCountry ("UK") where Cust.Orders.Count > 5 select Cust; This will end up doing a query in T-SQL which calls the TVG CustomerSelByCountry and do a WHERE clause against it and only then return the results to the client. I checked the Query Plan and it does look like two selects take place though i.e. the TVF runs first and then the second query which does the WHERE clause. So it's probably not as quick to run as a custom function or SP which does the entire query in one go, but I think it's a decent half-way house. You get the flexibility of using LINQ to write flexible queries on your data, yet you can secure you data through TVFs or SPs. I think what we'll end up doing - if possible - is to let an application do selections of data against raw tables, but only allow modifications of data using SPs, which can be secured. Who knows.


Been using LINQ for a while, but just over objects and Data Sets. But now I'm experimenting with using it over SQL - there's a few really good blogs that I've seen that have aided my understanding of it. By far the best two video blogs that I've found are: - LINQ Overview - Excellent overview on LINQ and a good introduction to C#3 as well Mike Taulty's LINQ to Video - Series of videos which gives an explanation of Linq to SQL from bottom up I've been using it with a couple of dummy applications, and I've been impressed with the way that it works - very easy to use, quite seamless. I suppose that there's two parts to it: - The Object Relational Mapper. This maps data from SQL to classes, handles change tracking etc.. You can create these classes yourself or get VS2008 to do it for you. Linq-to-SQL. An implementation (I think!) of the IQueryable interface, which translates LINQ statements into TSQL. If we were to use LINQ to SQL directly, without stored procedures (but you can use the OR mapper with SPs quite easily), the first question we'd have is how does it perform instead using conventional SPs? And the second would be, how can we secure it i.e. you can lock down SPs against users etc., but if you are using a Windows Application which used LINQ queries, running in the Windows Identity, effectively you're opening up the entire table to those users. If they got hold of Management Studio, they'd have full access to those tables, not just via the stored procedures. Opinions welcome.