score:0
i think i figured out a solution that works for my situation. rather than wrapping sqlconnection and overriding open() to change databases, i'm passing the dbcontext a new sqlconnection and subscribing to the connection's statechanged event. when the state changes, i check to see if the connection has just been opened. if so, i call sqlconnection.changedatabase() to point it to the correct database. i tested this solution and it seems to work - i see only one connection pool for all the databases rather than one pool for each db that has been accessed.
i realize this isn't the ideal solution in an ideal application, but for how this application is structured i think it should make a decent improvement for relatively little cost.
score:0
i think, that the best way is making unitofwork pattern with repository pattern to work with entity framework. entity framework has firstasync, firstordefaultasync, this helped me to fix the same bug.
score:2
i don't think that's going to work off a single shared connection.
linq to sql works best with unit of work type connections - create your connection, do your atomically grouped work and close the connection as quickly as possible and reopen for the next task. if you do that then passing in a connection string (or using custom constructor that only passes a tablename) is pretty straight forward.
if factoring your application is a problem, you could use a getter to manipulate the cached datacontext 'instance' and instead create a new instance each time you request it instead of using the cached/shared instance and inject the connection string in the getter.
but - i'm pretty sure this will not help with your pooling issue though. the sql server driver caches connections based on different connection string values - since the values are still changing you're right back to having lots of connections active in the connection string cache, which likely will result in lots of cache misses and therefore slow connections.
Source: stackoverflow.com
Related Query
- Linq to Sql: Change Database for each connection
- Solution For Updating LINQ to SQL Files After Database Schema Change
- Linq to SQL DataContext fails to update Enum field in database for inherited objects
- LINQ to SQL for an existing database
- Determine the source DataContext for a Linq to Sql query
- How to work around the fact that Linq to SQL is using separate SQL DELETE statements for each item in DeleteAllOnSubmit
- linq to entities changing database connection string in code
- LinQ to SQL and where is the choice for all database
- Is DB_LINQ + Non-SQL Server Database an acceptable substitute for LINQ to SQL with SQL Server?
- Data through Linq - EntityFramework - SQL database connection does not 'refresh'
- source code for LINQ 101 samples
- How to check linq query result against Sql Server Database for data already exist or not?
- Select latest 'N' records on the right table for each record on left table of left join on linq to sql
- LINQ - take x objects from database for each object property value
- My Linq to Sql Insert code seems to work fine but I don't get a record in the database
- Equivalent C# LINQ code for SQL
- Linq to sql code for contains
- Using linq to select possible values for multiple database columns in one SQL query (EF6)
- Check Nullable for object insde LinQ to Sql code
- how to fetch data from database using linq query for relationship 1:N and N:N (between 3 entity) in asp.net mvc EF code first?
- Linq to SQL - Getting the last know history entry for each divice, prior to a certain date
- Instantiate empty IQueryable for use with Linq to sql
- How are people unit testing code that uses Linq to SQL
- Best way for retrieving single record results in LINQ to SQL
- What is the easiest way to find the LINQ statement for a SQL statement
- Steps for a beginner to run very basic linq to sql query using Linqpad
- Create Reusable Linq To SQL For Stored Procedures
- LINQ to SQL does not update when data has changed in database
- Scalable Contains method for LINQ against a SQL backend
- Linq to Sql - Set connection string dynamically based on environment variable
More Query from same tag
- Linq: Dictionary - OrderBy with preference Dictionary
- LINQ to Entities does not recognize the method 'Double Parse(System.String)' cannot be translated into a store expression C# asp.net
- linq query that is causing performance issues
- Issue with getting a special attribute in XPath
- writing linq where or statement
- LINQ how to handle null values
- How to project EF Linq result to IEnumerable<IBook> where Book Explicitly implements in IBook?
- .NET String parsing performance improvement - Possible Code Smell
- Using a variable ColumnName
- Use linq to remove elements in one list using a condition in another
- NHibernate Linq Select Projection - retrieving full entities with partial select
- Include many to one records in LINQ
- Linq GroupBy is producing duplicate keys
- How to join Consecutive Date Ranges in linq
- Linq generic GroupBy date and other fields at the same time
- Make zero appear last in a sorted list of integers
- Linq - Sum with isnull
- Entity Framework returning distinct records after join
- .NET Core 3 InvalidOperationException on OrderBy with dynamic field name
- How to getting distinct values by linq or lambda?
- What is the proper way of calling multiple "SQL DataReader"s in C#?
- find and delete whole line in txt file
- Looping LINQ queries on the same IEnumerable - what am I missing?
- Linq Not Working on a List of Strings
- SortedList<Key, Value>: Error while changing the values inside a loop, while looping through the list / Unity, C#
- Return anonymous type results when using linq
- C# LINQ get records from datatable group by hour
- rounding decimal number in linq
- Distinct in combination with pagination
- Joining returning null if one of the column is null
