The following sections answer some common issues that you might encounter when you implement LINQ.
Additional issues are addressed in Troubleshooting.
I cannot connect to my database.
Make sure your connection string is correct and that your SQL Server instance is running. Note also that LINQ to SQL requires the Named Pipes protocol to be enabled. For more information, see Learning by Walkthroughs.
Changes to Database Lost
I made a change to data in the database, but when I reran my application, the change was no longer there.
Make sure that you call SubmitChanges to save results to the database.
Database Connection: Open How Long?
How long does my database connection remain open?
A connection typically remains open until you consume the query results. If you expect to take time to process all the results and are not opposed to caching the results, apply ToList to the query. In common scenarios where each object is processed only one time, the streaming model is superior in both
DataReader and LINQ to SQL.
The exact details of connection usage depend on the following:
Updating Without Querying
Can I update table data without first querying the database?
Although LINQ to SQL does not have set-based update commands, you can use either of the following techniques to update without first querying:
Unexpected Query Results
My query is returning unexpected results. How can I inspect what is occurring?
Unexpected Stored Procedure Results
I have a stored procedure whose return value is calculated by `MAX()`. When I drag the stored procedure to the O/R Designer surface, the return value is not correct.
LINQ to SQL provides two ways to return database-generated values by way of stored procedures:
By naming the output result.
By explicitly specifying an output parameter.
The following is an example of incorrect output. Because LINQ to SQL cannot map the results, it always returns 0:
create procedure proc2 as begin select max(i) from t where name like 'hello' end
The following is an example of correct output by using an output parameter:
create procedure proc2 @result int OUTPUT as select @result = MAX(i) from t where name like 'hello' go
The following is an example of correct output by naming the output result:
create procedure proc2 as begin select nax(i) AS MaxResult from t where name like 'hello' end
For more information, see Customizing Operations By Using Stored Procedures.
When I try to serialize, I get the following error: "Type 'System.Data.Linq.ChangeTracker+StandardChangeTracker' ... is not marked as serializable."
Multiple DBML Files
When I have multiple DBML files that share some tables in common, I get a compiler error.
Set the Context Namespace and Entity Namespace properties from the Object Relational Designer to a distinct value for each DBML file. This approach eliminates the name/namespace collision.
Avoiding Explicit Setting of Database-Generated Values on Insert or Update
I have a database table with a `DateCreated` column that defaults to SQL `Getdate()`. When I try to insert a new record by using LINQ to SQL, the value gets set to `NULL`. I would expect it to be set to the database default.
LINQ to SQL handles this situation automatically for identity (auto-increment) and rowguidcol (database-generated GUID) and timestamp columns. In other cases, you should manually set IsDbGenerated=
true and AutoSync=Always/OnInsert/OnUpdate properties.
Can I specify additional load options without overwriting the first?
Yes. The first is not overwritten, as in the following example:
Dim dlo As New DataLoadOptions() dlo.LoadWith(Of Order)(Function(o As Order) o.Customer) dlo.LoadWith(Of Order)(Function(o As Order) o.OrderDetails)
DataLoadOptions dlo = new DataLoadOptions(); dlo.LoadWith<Order>(o => o.Customer); dlo.LoadWith<Order>(o => o.OrderDetails);
Errors Using SQL Compact 3.5
I get an error when I drag tables out of a SQL Server Compact 3.5 database.
The Object Relational Designer does not support SQL Server Compact 3.5, although the LINQ to SQL runtime does. In this situation, you must create your own entity classes and add the appropriate attributes.
Errors in Inheritance Relationships
I used the toolbox inheritance shape in the Object Relational Designer to connect two entities, but I get errors.
Creating the relationship is not enough. You must provide information such as the discriminator column, base class discriminator value, and derived class discriminator value.
Is a public provider model available?
No public provider model is available. At this time, LINQ to SQL supports SQL Server and SQL Server Compact 3.5 only.
How is LINQ to SQL protected from SQL-injection attacks?
SQL injection has been a significant risk for traditional SQL queries formed by concatenating user input. LINQ to SQL avoids such injection by using SqlParameter in queries. User input is turned into parameter values. This approach prevents malicious commands from being used from customer input.
Changing Read-only Flag in DBML Files
How do I eliminate setters from some properties when I create an object model from a DBML file?
Take the following steps for this advanced scenario:
In the .dbml file, modify the property by changing the IsReadOnly flag to
Add a partial class. Create a constructor with parameters for the read-only members.
If you are using the Object Relational Designer in Visual Studio, your changes might be overwritten.
Is System.Data.Linq marked for use by partially trusted code?
Yes, the System.Data.Linq.dll assembly is among those .NET Framework assemblies marked with the AllowPartiallyTrustedCallersAttribute attribute. Without this marking, assemblies in the .NET Framework are intended for use only by fully trusted code.
The principal scenario in LINQ to SQL for allowing partially trusted callers is to enable the LINQ to SQL assembly to be accessed from Web applications, where the trust configuration is Medium.
Mapping Data from Multiple Tables
The data in my entity comes from multiple tables. How do I map it?
You can create a view in a database and map the entity to the view. LINQ to SQL generates the same SQL for views as it does for tables.
The use of views in this scenario has limitations. This approach works most safely when the operations performed on Table<TEntity> are supported by the underlying view. Only you know which operations are intended. For example, most applications are read-only, and another sizeable number perform
Delete operations only by using stored procedures against views.
Is there a construct that can help with DataContext pooling?
Do not try to reuse instances of DataContext. Each DataContext maintains state (including an identity cache) for one particular edit/query session. To obtain new instances based on the current state of the database, use a new DataContext.
You can still use underlying ADO.NET connection pooling. For more information, see SQL Server Connection Pooling (ADO.NET).
Second DataContext Is Not Updated
I used one instance of DataContext to store values in the database. However, a second DataContext on the same database does not reflect the updated values. The second DataContext instance seems to return cached values.
This behavior is by design. LINQ to SQL continues to return the same instances/values that you saw in the first instance. When you make updates, you use optimistic concurrency. The original data is used to check against the current database state to assert that it is in fact still unchanged. If it has changed, a conflict occurs and your application must resolve it. One option of your application is to reset the original state to the current database state and to try the update again. For more information, see How to: Manage Change Conflicts.
You can also set ObjectTrackingEnabled to false, which turns off caching and change tracking. You can then retrieve the latest values every time that you query.
Cannot Call SubmitChanges in Read-only Mode
When I try to call SubmitChanges in read-only mode, I get an error.
Read-only mode turns off the ability of the context to track changes.