LINQ to SQL
Troubleshooting (LINQ to SQL)

The following information exposes some issues you might encounter in your LINQ to SQL applications, and provides suggestions to avoid or otherwise reduce the effect of these issues.

Additional issues are addressed in Frequently Asked Questions (LINQ to SQL).

Unsupported Standard Query Operators

LINQ to SQL does not support all standard query operator methods (for example, ElementAt<(Of <(TSource>)>)). As a result, projects that compile can still produce run-time errors. For more information, see Standard Query Operator Translation (LINQ to SQL).

Memory Issues

If a query involves an in-memory collection and LINQ to SQL Table<(Of <(TEntity>)>), the query might be executed in memory, depending on the order in which the two collections are specified. If the query must be executed in memory, then the data from the database table will need to be retrieved.

This approach is inefficient and could result in significant memory and processor usage. Try to avoid such multi-domain queries.

File Names and SQLMetal

To specify an input file name, add the name to the command line as the input file. Including the file name in the connection string (using the /conn option) is not supported. For more information, see Code Generation Tool (SqlMetal.exe).

Class Library Projects

The Object Relational Designer creates a connection string in the app.config file of the project. In class library projects, the app.config file is not used. LINQ to SQL uses the Connection String provided in the design-time files. Changing the value in app.config does not change the database to which your application connects.

Cascade Delete

LINQ to SQL does not support or recognize cascade-delete operations. If you want to delete a row in a table that has constraints against it, you must do either of the following:

  • Set the ON DELETE CASCADE rule in the foreign-key constraint in the database.

  • Use your own code to first delete the child objects that prevent the parent object from being deleted.

Otherwise, a SqlException exception is thrown.

For more information, see How to: Delete Rows From the Database (LINQ to SQL).

Expression Not Queryable

If you get the "Expression [expression] is not queryable; are you missing an assembly reference?" error, make sure of the following:

  • Your application is targeting .NET Compact Framework 3.5.

  • You have a reference to System.Core.dll and System.Data.Linq.dll.

  • You have an Imports (Visual Basic) or using (C#) directive for System.Linq and System.Data.Linq.

DuplicateKeyException

In the course of debugging a LINQ to SQL project, you might traverse an entity's relations. Doing so brings these items into the cache, and LINQ to SQL becomes aware of their presence. If you then try to execute Attach or InsertOnSubmit or a similar method that produces multiple rows that have the same key, a DuplicateKeyException is thrown.

String Concatenation Exceptions

Concatenation on operands mapped to [n]text and other [n][var]char is not supported. An exception is thrown for concatenation of strings mapped to the two different sets of types. For more information, see System.String Methods (LINQ to SQL).

Skip and Take Exceptions in SQL Server 2000

You must use identity members (IsPrimaryKey) when you use Take<(Of <(TSource>)>) or Skip<(Of <(TSource>)>) against a SQL Server 2000 database. The query must be against a single table (that is, not a join), or be a Distinct, Except, Intersect, or Union operation, and must not include a Concat<(Of <(TSource>)>) operation. For more information, see the "SQL Server 2000 Support" section in Standard Query Operator Translation (LINQ to SQL).

This requirement does not apply to SQL Server 2005.

GroupBy InvalidOperationException

This exception is thrown when a column value is null in a GroupBy query that groups by a boolean expression, such as group x by (Phone==@phone). Because the expression is a boolean, the key is inferred to be boolean, not nullable boolean. When the translated comparison produces a null, an attempt is made to assign a nullable boolean to a boolean, and the exception is thrown.

To avoid this situation (assuming you want to treat nulls as false), use an approach such as the following:

GroupBy="(Phone != null) && (Phone=@Phone)"

OnCreated() Partial Method

The generated method OnCreated() is called each time the object constructor is called, including the scenario in which LINQ to SQL calls the constructor to make a copy for original values. Take this behavior into account if you implement the OnCreated() method in your own partial class.

See Also

Concepts

Frequently Asked Questions (LINQ to SQL)

Other Resources

Debugging Support (LINQ to SQL)
Tags :


Community Content

Steve G_
How to use Skip and Take in SQL 2000
"This provider supports Skip() only over ordered queries returning entities or projections that contain all identity columns, where the query is a single-table (non-join) query, or is a Distinct, Except, Intersect, or Union (not Concat) operation."

You will get this error trying to use Skip/Take in SQL 2000, and it may seem that SQL 2000 doesn't support Skip/Take when you're joining tables. Actually it does, and here's how to do it:

First, some tables

      
Create table Departments

      
{

      
    int idDept Identity (1,1) Primary Key not null, 
    varchar(50) DepartmentName,
    varchar(50) ManagerName
}

Create table Employees
{
    int idEmp Identity(1,1) Primary key not null, 
    int idDept References Departments(idDept), 
    varchar(50) EmployeeName
}

Create table addresses
{
    int idAddr Identity(1,1) Primary key not null,
    int idEmp References Employees(idEmp),
    varchar(255) Address
}

Next, let's assume that we have a Linq to SQL data context created called EmployeeDataContext, that contains these tables. And finally, some code to explain how to use Skip/Take with SQL 2000


// Our search results type
public class EmployeeData
{
    public string Name
    public string Address
}

public class EmployeeSearch
{
    // find names and addresses of employees by manager name or partial name
    public IEnumerable<EmployeeData> getNamesandAddresses(string MgrName, 
        int PageNo, int perPage)
    {
        EmployeeDataContext DC = new EmployeeDataContext();

        // Here's the secret sauce. The trick is to do this in two steps.
        // First step is to select the primary keys ONLY of the rows 
        // we're interested in. Skip/Take works over Primary keys in a Distinct and 
        // Ordered query - see that error message above!
        var uniquerows = (from d in DC.Departments
        join e in DC.Employees on d.idDept equals e.idDept
        join a in DC.Addresses on e.idEmp equals a.idEmp
        where d.ManagerName.Contains(MgrName)
        select new { d.idDept, e.idEmp, a.idAddr })
        .Distinct().Orderby(n=>n.idDept).Skip(perPage * (PageNo-1))
        .Take(perPage);

        // Part two of how to do this: once we have the keys of the distinct rows,
        // now we go and get the data by joining our results above with the tables 
        // that contain the data we want
        IEnumerable<EmployeeData> retvalues = 
            (from row in uniquerows
            join e in DC.Employees on row.idEmp equals e.idEmp
            join a in DC.Addresses on row.idAddr equals a.idAddr
            select new EmployeeData{ Name=e.EmployeeName, Address=a.Address })
            .AsEnumerable();

        return retvalues;
    }
}

Problems with this techique: While this will work in sql 2000, it's slow because we are doing the work twice - once to identify the unique rows that we want and once to get the data. 

Good luck and I hope this helps someone!

Steve G.

Page view tracker