Frequently Asked Questions (LINQ to SQL)
The following sections answer some common issues that you might encounter when you implement LINQ.
Additional issues are addressed in Troubleshooting (LINQ to SQL).
Q. I cannot connect to my database.
A. 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 (LINQ to SQL).
Q. I made a change to data in the database, but when I reran my application, the change was no longer there.
A. Make sure that you call SubmitChanges to save results to the database.
Q. How long does my database connection remain open?
A. 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(Of TSource) 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:
Q. Can I update table data without first querying the database?
A. Although LINQ to SQL does not have set-based update commands, you can use either of the following techniques to update without first querying:
Q. 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.
A. 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
select max(i) from t where name like 'hello'
The following is an example of correct output by using an output parameter:
create procedure proc2
@result int OUTPUT
select @result = MAX(i) from t where name like 'hello'
The following is an example of correct output by naming the output result:
create procedure proc2
select nax(i) AS MaxResult from t where name like 'hello'
For more information, see Customizing Operations By Using Stored Procedures (LINQ to SQL).
Q. When I try to serialize, I get the following error: "Type 'System.Data.Linq.ChangeTracker+StandardChangeTracker' ... is not marked as serializable."
Q. 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.
A. 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.
Q. How is LINQ to SQL protected from SQL-injection attacks?
A. 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.
Q. How do I eliminate setters from some properties when I create an object model from a DBML file?
A. Take the following steps for this advanced scenario:
In the .dbml file, modify the property by changing the IsReadOnly flag to True.
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.
Q. Is System.Data.Linq marked for use by partially trusted code?
A. 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.
Q. The data in my entity comes from multiple tables. How do I map it?
A. 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(Of 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 Create/Update/Delete operations only by using stored procedures against views.
Q. Is there a construct that can help with DataContext pooling?
A. 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).
Q. 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.
A. 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 (LINQ to SQL).
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.
Q. When I try to call SubmitChanges in read-only mode, I get an error.
A. Read-only mode turns off the ability of the context to track changes.