LINQ to SQL: Single or Multiple .dbml (Sprocs)

Question

i have read about the single vs multiple dbml but it was geared towards using tables but my questions is very specific to using sproc for multiple dbmls i have around 10 lookup sprocs and other i have getList/insert/update/delete .... the list is growing like crazy and its getting hard to keep eye on it and i am easily spending lot of time scanning the sprocs...

is that okay to have multiple dbmls if you are using sprocs only? so i am planning to have two separate dbmls 1) lookups 2) GetLists / Insert / Delete / Update

any thoughts?

Answer 1

I don't believe you can share DataContexts if you have multiple DBMLs. And I also think that if you have two DataContexts, they don't by default share the same connection, so you're more likely to promote to a distributed transaction in certain usage patterns.

So I would recommend against using multiple DBML files. If you really want that capability, upgrade to the Entity Framework 4.0, which, as I understand it, allows you to have the multiple EDMX files which share the same DataContext.

Answer 2

I ran into a situation recently where I had a library project that performed operations on certain tables in a database. I used SqlMetal to generate a DataContext code file (I didn't bother with a dbml file, though I don't think that would hurt).

I then built an app that used this library. This app performed operations on the same tables as the library, but also added several other tables (which either referenced the tables used by the library or referenced each other). I used SqlMetal to generate a DataContext code file for the apps. So, I now had two different DataContexts for the same database.

When I put everything together, I was a little worried, but it worked like a charm. Even though both the library DataContext and the app DataContext were accessing the same database using different connection objects (but with the same connection string), there were no noticeable problems.

This may not be recommended practice, and it might not be suitable for a large-scale app, but it wasn't a problem in this situation. I should note that I'm careful to always use the using keyword each time I perform an operation on a DataContext to make sure connections get disposed properly.

Note: I didn't use sprocs, only standard select, insert, update, delete, but I would think you'd get similar results using sprocs.