Frequently Asked Questions

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.

Make sure that you call SubmitChanges to save results to the database.

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:

• 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).

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.

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 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.

Code generation in LINQ to SQL supports DataContractSerializer serialization. It does not support XmlSerializer or BinaryFormatter. For more information, see Serialization.

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.

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.

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);

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.

Creating the relationship is not enough. You must provide information such as the discriminator column, base class discriminator value, and derived class discriminator value.

No public provider model is available. At this time, LINQ to SQL supports SQL Server and SQL Server Compact 3.5 only.

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.

Take the following steps for this advanced scenario:

1. In the .dbml file, modify the property by changing the IsReadOnly flag to True.

2. Add a partial class. Create a constructor with parameters for the read-only members.

3. Review the default UpdateCheck value (Never) to determine whether that is the correct value for your application.

   Caution

   If you are using the Object Relational Designer in Visual Studio, your changes might be overwritten.

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.

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.

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).

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.

Read-only mode turns off the ability of the context to track changes.

See also

• Reference
• Troubleshooting
• Security in LINQ to SQL