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).
Cannot Connect
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).
Changes to Database Lost
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.
Database Connection: Open How Long?
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<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:
Connection status if the DataContext is constructed with a connection object.
Connection string settings (for example, enabling Multiple Active Result Sets (MARS). For more information, see Multiple Active Result Sets (MARS).
Updating Without Querying
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:
Use ExecuteCommand to send SQL code.
Create a new instance of the object and initialize all the current values (fields) that affect the update. Then attach the object to the DataContext by using Attach and modify the field you want to change.
Unexpected Query Results
Q. My query is returning unexpected results. How can I inspect what is occurring?
A. LINQ to SQL provides several tools for inspecting the SQL code it generates. One of the most important is Log. For more information, see Debugging Support (LINQ to SQL).
Unexpected Stored Procedure Results
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
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 (LINQ to SQL).
Serialization Errors
Q. When I try to serialize, I get the following error: "Type 'System.Data.Linq.ChangeTracker+StandardChangeTracker' ... is not marked as serializable."
A. Code generation in LINQ to SQL supports DataContractSerializer serialization. It does not support XmlSerializer or BinaryFormatter. For more information, see Serialization (LINQ to SQL).
Multiple DBML Files
Q. When I have multiple DBML files that share some tables in common, I get a compiler error.
A. 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
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.
Multiple DataLoadOptions
Q. Can I specify additional load options without overwriting the first?
A. 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
Q. I get an error when I drag tables out of a SQL Server Compact 3.5 database.
A. 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
Q. I used the toolbox inheritance shape in the Object Relational Designer to connect two entities, but I get errors.
A. Creating the relationship is not enough. You must provide information such as the discriminator column, base class discriminator value, and derived class discriminator value.
Provider Model
Q. Is a public provider model available?
A. No public provider model is available. At this time, LINQ to SQL supports SQL Server and SQL Server Compact 3.5 only.
SQL-Injection Attacks
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.
Changing Read-only Flag in DBML Files
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.
Review the default UpdateCheck value (Never) to determine whether that is the correct value for your application.
.gif "Caution note")
CautionIf you are using the Object Relational Designer in Visual Studio, your changes might be overwritten.
APTCA
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.
For more information, see ASP.NET Code Access Security and ASP.NET Code Access Security.
Mapping Data from Multiple Tables
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.
Note
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 Create/Update/Delete operations only by using stored procedures against views.
Connection Pooling
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).
Second DataContext Is Not Updated
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.
Cannot Call SubmitChanges in Read-only Mode
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.