Linq: join or not join (this is the best way, association or relationship)

Question

I wrote quite a lot of code that uses the Linq2Sql-related tables provided to me, only having foreign keys in my database. But, it turns out to be a little time consuming to mock the data for my unit tests. I have to manually establish any relationship in my test harness.

So, I wonder if Linq joins, rather than relying on relationships, will give me a more easily verifiable and possibly more efficient code.

var query = from orderItem in data.OrderItems select new { orderItem.Order.Reference, orderItem.SKU, orderItem.Quantity, }; Console.WriteLine("Relationship Method"); query.ToList().ForEach(x => Console.WriteLine(string.Format("Reference = {0}, {1} x {2}", x.Reference, x.Quantity, x.SKU))); var query2 = from orderItem in data.OrderItems join order in data.Orders on orderItem.OrderID equals order.OrderID select new { order.Reference, orderItem.SKU, orderItem.Quantity, }; Console.WriteLine(); Console.WriteLine("Join Method"); query2.ToList().ForEach(x => Console.WriteLine(string.Format("Reference = {0}, {1} x {2}", x.Reference, x.Quantity, x.SKU))); 

Both queries above give me the same result, but is one better than the other in terms of performance and in terms of validation?

Answer 1

What are you testing? Linq to SQL data reading vulnerability? It is generally believed that linq to sql is a thin veneer over the database, that linq to sql code itself is considered “intact” and therefore does not need to be tested.

I really don't want to complicate my code so that you can mock linq in sql DBML. If you want to test your business logic, it is much better to simply connect the test database to DBML (there is a constructor overload for the datacontext that allows you to do this) and use database transactions to verify your interactions with the data. Thus, you can undo a transaction to undo changes to the database, leaving the test database in its original state.

Answer 2

In terms of performance, both queries will be evaluated with the same SQL (Scott Guthrie has a blog post on how to view generated SQL for LINQ queries). I do not think that any option is inherently more "tested" than the other. However, I prefer to use foreign keys and relationships, because when using SQL Metal, it allows you to quickly find out that your database has the corresponding keys.

Answer 3

I do not think that any approach has an advantage in performance or testability. The first form is easier to read, and therefore I personally will agree with this. This is a subjective question.

It seems to me that your problem is that you can easily customize your data and save the values ​​of the foreign key and entity references. I do not think this is easy to solve. You can write some kind of framework that creates object proxies, and uses the metadata of the object to intercept the FK and related objects to determine the properties of the object in order to synchronize them, but before you know it, you implemented the database in memory!