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