Linq-to-SQL: how many datacontexts?

Question

I have a SQL Server 2008 database with > 300 tables. The application I have to design is an Windows Forms app, .NET 3.5, C#.

Which is the best way to work with Linq-to-SQL ?

I intend to make a datacontext for each business entity.

Is there any problem ?

I need to know if this way of working with Linq-to-SQL has any disadvantage or can create performance issues ?

Thanks.

http://www.west-wind.com/weblog/posts/246222.aspx – Gregoire May 29 '10 at 14:30 — Gregoire, May 29 '10 at 14:30

score 8 · Accepted Answer · answered May 29 '10 at 15:54

You should typically have 1 single DBML file (=data context) per database. You should certainly not create a DataContext per business entity, because doing this would make you lose most of the useful capabilities of LINQ to SQL, like memory transactions (unit of work), lazy loading, and doing LINQ queries over multiple entities.

You have a pretty big model (+300 tables) which means a lot of entities. A lot of entities is not a big problem, except for the LINQ to SQL designer. Using the designer with such big models can be pretty annoying. This can be a reason to split a domain in multiple sub domains (with each a DBML file), but certainly not one per entity. However, keep in mind that you loose the L2S capabilities at the boundaries of the domains.

In the past I advised a team, who had split up their +150 entities domain in 5 DBML files, to merge them back together to a single DBML. The pain of editing the model went up, but the pain of using multiple DataContexts went away, which lowered the overall pain drastically for them.

Thanks. Please, what exactly do you mean by "The pain of editing the model" ? I think that splitting a big datamodel in sub domains can solve some performance problems. Do you have experience in working with big datamodel/single DBML file in win forms/SQL Server database that requires 100+ concurrent users ? Thanks. — sh00, May 30 '10 at 08:24
The number of data context classes you create will have no effect whatever on performance. — Ben Robinson, May 30 '10 at 11:45
I agree with Ben. I can't imagine having multiple DBML's have any impact on performance. Please explain why you think that. — Steven, May 30 '10 at 14:09

score 6 · Answer 2 · answered May 29 '10 at 14:35

6

There is no point in making a data context for each business entity, you only need one datacontext per database.

answered May 29 '10 at 14:35

Ben Robinson

21,601
5
62
79

score 0 · Answer 3 · answered May 29 '10 at 14:36

0

well it depends on how many users will use your database simultaneously not how many tables are there. So its all about typical database issues: number of connections, locking and other stuff.

answered May 29 '10 at 14:36

Voice

1,547
16
31

I don't think he is talking about instantiating a `DataContext`, but defining a DBML file per entity. – Steven May 29 '10 at 15:56
Yes, defining a DMBL file per entity. Thanks. – sh00 May 30 '10 at 08:15

score 0 · Answer 4 · answered Apr 15 '11 at 10:44

I now use 1 for the entire database, but there are legitimate uses for having more. For example, I run a script when installing my site that connects to a remote DB and imports and converts data to the new format for deployment. The process uses some temporary tables.

By putting the temporary tables in a separate context, once the site is deployed I can simply delete these contexts and code as they are independent entities.

Linq-to-SQL: how many datacontexts?

4 Answers4

Linked