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.

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.
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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s