Challenges with Linq to sql concept in dot net

Question

Hi, Let say if I used the Linq to Sql concept to interact with database from C# language , then what challenges I may be face? means in terms of architecture, performance , type safety, objects orientation etc ..!

Answer 1

Basically Linq to SQL generates a class for each table in your database, complete with relation properties and all, so you will have no problems with type safety. The use of C# partials allows you to add functionality to these objects without messing around with Linq to SQLs autogenerated code. It works pretty well.

As tables map directly to classes and objects, you will either have to accept that your domain layer mirrors the database design directly, or you will have to build some form of abstraction layer above Linq to SQL. The direct mirroring of tables can be especially troublesome with many-to-many relations, which is not directly supported - instead of Orders.Products you get Order.OrderDetails.SelectMany(od => od.Product).

Unlike most other ORMs Linq to SQL does not just dispense objects from the database and allow you to store or update objects by passing them back into the ORM. Instead Linq to SQL tracks the state of objects loaded from the database, and allows you to change the saved state. It is difficult to explain and strange to understand - I recommend you read some of Rick Strahls blogposts on the subject.

Performance wise Linq-to-SQL does pretty good. In benchmarking tests it shows speeds of about 90-95% of what a native SQL reader would provide, and in my experience real world usage is also pretty fast. Like all ORMs Linq to SQL is affected by the N+1 selects problem, but it provides good ways to specify lazy/eager loading depending on context.

Also, by choosing Linq to SQL you choose MSSQL - there do exist third party solutions that allow you to connect to other databases, but last time I checked, none of them appeared very complete.

All in all, Linq to SQL is a good and somewhat easy to learn ORM, which performs okay. If you need features beyond what Linq to SQL is offering, take a look at the new entity framework - it has more features, but is also more complex.

Answer 2

We've had a few challenges, mainly from opening the query construction capability to programmers that don't understand how databases work. Here are a few smells:

//bad scaling
//Query in a loop - causes n roundtrips
//  when c roundtrips could have been performed.
List<OrderDetail> od = new List<OrderDetail>();
foreach(Customer cust in customers)
{
  foreach(Order o in cust.Orders)
  {
    od.AddRange(dc.OrderDetails.Where(x => x.OrderId = o.OrderId));
  }
}

//no seperation of
//  operations intended for execution in the database
//  from operations intended to be executed locally
var query =
  from c in dc.Customers
  where c.City.StartsWith(textBox1.Text)
  where DateTime.Parse(textBox2.Text) <= c.SignUpDate
  from o in c.Orders
  where o.OrderCode == Enum.Parse(OrderCodes.Complete)
  select o;

//not understanding when results are pulled into memory
//  causing a full table load
List<Item> result = dc.Items.ToList().Skip(100).Take(20).ToList();

Another problem is that one more level of separation from the table structures means indexes are even easier to ignore (that's a problem with any ORM though).