Question What database option to choose when there are many relations between tables? Entity Framework, ADO.NET, Linq to SQL...

janter

New member
Joined
Apr 25, 2019
Messages
4
Programming Experience
3-5
I'm working on a desktop application in C#, using WPF for the user interface. It's an inventory management system, and it handles a lot of relational data.
For the database I use SQL Server.

I wonder what technology is best suitable for when there are many database tables that are related to each other.
For example, we have a table called Purchase, which contains the data of a placed orders to a supplier. Another table called OrderedProducts contains all products that belong to each Purchase. Every ordered product in the OrderedProducts table has an Id that belongs to the purchase row in the Purchase table. A purchase can have many ordered products.

The above example is very common. You see several possibilities on the internet. But what most do, is just creating single queries in loops. When I want a list of multiple purchases including the corresponding ordered products, they create a loop querying a purchase row from the Purchase table, getting the purchase Id, and then they query the ordered products from the OrderedProducts table. This has two disadvantages: loading one purchase with its products, needs two queries while it could be reduced to one query if it would be selected using a JOIN.
Another disadvantage is that a loop in code is used. Also when writing a good JOIN query you could select all purchases and the corresponding ordered products, in one single query. Without any loop.

I can not choose between the available database interaction technologies in .NET C#: Entity Framework, ADO.NET or Linq to SQL.
Linq to SQL seems to be able to handle JOIN-alike data relations nowadays. But you don't see how it queries the database under the hood. Maybe it does just the same as in the example, and queries the database within loops which it not the most efficient.

What are your experiences? And what's the best way to work with relational data?
 
I pretty much never use LINQ to SQL as it is limited. I use ADO.NET for quick and dirty demos and the like. For any serious app, I use EF. That's not to say that you can't use the other two but EF is powerful and flexible and easy to use in most cases. For joins, you simply use the Include function to specify that related entities should be included in the result set. You can also view the generated SQL if you want to, plus you can write your own SQL if you find you need that for a particular scenario.
 
Back
Top Bottom