LINQ query should not include your class projection to improve performance

Possibly save 1 hour of your time: I have found that performance of your LINQ query could be impact especially for large datasets when you project your results directly to a class object. It is better to project to .NET object after the sql query results came back first.

var results = (from a in context.TableA
                    join b in context.TableB on a.ID equals b.ID
                    select new YourClassName
                    {
                        Year = a.Year,
                        Name = b.Name
                    });
var list = results.ToList();

The code above is much slower than the code below for large datasets.

var results = (from a in context.TableA
                    join b in context.TableB on a.ID equals b.ID
                    select new
                    {
                        Year = a.Year,
                        Name = b.Name
                    });
var list = results.ToList();

var returnList = list.Select(x => new YourClassName
                {
                    Year = x.Year,
                    Name = x.Name
                }).ToList();

 

Advertisements

LINQ SQL – FULL OUTER JOIN

Possibly save 1 hour of your time: There is no equivalent of full outer join in LINQ that is same as SQL.

The solution is to do a left outer join and then a right outer join using DefaultIfEmpty and then use UNION to join these two results.

var leftouterjoin = (from items1 in table1
                    join items2 in table2
                    on new { items1.column1, items1.column2 } equals new { items2.column1, items2.column2 } into tt
                    from ttnew in tt.DefaultIfEmpty()
                    select new { column1= items1.column1, column2 = items1.column2, Value = items1.Value });

var rightouterjoin = (from items2 in table2
                    join items1 in table1
                    on new { items2.column1, items2.column2 } equals new { items1.column1, items1.column2 } into tt
                    from ttnew in tt.DefaultIfEmpty()
                    select new { column1= items2.column1, column2 = items2.column2, Value = items2.Value });

                var fullouterjoin = leftouterjoin.Union(rightouterjoin).ToList();

                var returnList = fullouterjoin.Select(x => new YourClass
                {
                    column1= x.column1,
                    column2= x.column2,
                    Value = x.Value,
                }).ToList();

If you have null values, your UNION may return duplicates. The key is to add Distinct() after your UNION().

There is a difference between SQL UNION (which performs an implicit DISTINCT) and LINQ Union (which required an explicit Distinct()).

LINQ System.NotSupportedException – The entity or complex type cannot be constructed in a LINQ to Entities query.

Possibly save 1 hour of your time: In LINQ to SQL syntax, you cannot project into your class directly. In the example below, we also show how to do joins on multiple columns using anonymous type.

You may get the following exception:

  • System.NotSupportedException : The entity or complex type ‘YourObject’ cannot be constructed in a LINQ to Entities query.

You cannot select new class from your class directly. You need to store result on a variable and execute the query and then use LINQ lambda syntax to project to your class.

 

var list = (from items1 in table1
join items2 in table2
on new { items1.column1, items1.column2} equals new { items2.column1, items2.column2}
select new { column1 = items1.column1, column2 = items1.column2, value = items2.Value }
).ToList();

var returnList = list.Select(x => new YourClass
{
column1= x.column1,
column2= x.column2,
Value = x.Value
}).ToList();

 

LINQ and Entity Framework Errors

Possibly save 4 hours of your time: When working with LINQ and Entity Framework 6, you may encounter the following errors.

  1. Only parameterless constructors and initializers are supported in LINQ to Entities.
  2. The ‘Distinct’ operation cannot be applied to the collection ResultType of the specified argument. Parameter name: argument
    This happens on Union operation of two entities with different schemas
  3. An error occurred while preparing the command definition. See the inner exception for details.
    This happens on Union operation of Concat operation on two entities with different schemas
  4. System.InvalidOperationException : The specified cast from a materialized ‘System.Int32’ type to the ‘System.String’ type is not valid.
    This happens on Union or Concat operation on two entities with different schemas.

First one is obvious, you must have parameterless constructor when selecting new object transformation.

from i in dbcontext.TableName

select new EntityObjectName() { ID = i.ID }

Second and Third one is similar. When you select new object with properties that are in array form, you may get these errors. One idea would be to simply change this to IEnumerable or IList.

Fourth, this one took me awhile to figure out. It basically happens when the property being transformed came from two different tables and have different data types on the database backend. A simple fix is to check for null and turn into empty string on both the union/concat select. This seems to fix the issue.

from i in dbcontext.TableName

select new EntityObjectName() { Value = i.Value ?? “” }

UNION or CONCAT

from j in dbcontext.TableName2

select new EntityObjectName() { Value = j.Value2 ?? “” }

Programming Entity Framework: Code First: Creating and Configuring Data Models from Your Classes