Linq vs. database views

Question

Here’s an interesting question. Suppose we have related tables in the database, for example, Instrument and Currency. Instrument table has a currency_id field that is mapped to entry in Currency table. In Linq land what’s the better way:

a) Create Instrument and Currency entities in the DataContext and then create association or simply use join in Linq queries or

b) Create a view in the database that joins Instrument and Currency (thus resolving currency_id to currency code) and use that as an entity in Linq context?

Answer 1

Would you ever use them independently? If so, you will need to have entities for each one that will be used independently. I suspect that you will use the Currency independently (say for a dropdown that allows you to choose a currency when creating an instrument). That being the case, I think it would be easier to just keep them separate and have an association.

Answer 2

With the ORM now abstracting the data access logic that particular function of views is no longer needed. It would be best to leave it to the ORM since thats part of its function.

However views may still be useful to simplify stored procedures code and even for creating useful indices.

Answer 3

• If you load in Instrument and later use the Currencies property to load in related Currencies, there will be two queries.

• If you issue a linq query with a join, linq will convert that to sql with a join and you get all the data at once.

• If you set up a DataLoadOptions, you get all the data in one query and you do not have to write the join.

http://msdn.microsoft.com/en-us/library/system.data.linq.dataloadoptions.aspx http://msdn.microsoft.com/en-us/library/system.data.linq.dataloadoptions.loadwith.aspx

DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<Instrument>(i => i.Currencies)
myDataContext.LoadOptions = dlo;