Compare nullable types in Linq to Sql
The first thing to do is to put on logging, to see what TSQL was generated; for example:
ctx.Log = Console.Out;
LINQ-to-SQL seems to treat nulls a little inconsistently (depending on literal vs value):
using(var ctx = new DataClasses2DataContext())
{
ctx.Log = Console.Out;
int? mgr = (int?)null; // redundant int? for comparison...
// 23 rows:
var bosses1 = ctx.Employees.Where(x => x.ReportsTo == (int?)null).ToList();
// 0 rows:
var bosses2 = ctx.Employees.Where(x => x.ReportsTo == mgr).ToList();
}
So all I can suggest is use the top form with nulls!
i.e.
Expression<Func<Category,bool>> predicate;
if(categoryId == null) {
predicate = c=>c.ParentId == null;
} else {
predicate = c=>c.ParentId == categoryId;
}
var subCategories = this.Repository.Categories
.Where(predicate).ToList().Cast<ICategory>();
Update - I got it working "properly" using a custom Expression
:
static void Main()
{
ShowEmps(29); // 4 rows
ShowEmps(null); // 23 rows
}
static void ShowEmps(int? manager)
{
using (var ctx = new DataClasses2DataContext())
{
ctx.Log = Console.Out;
var emps = ctx.Employees.Where(x => x.ReportsTo, manager).ToList();
Console.WriteLine(emps.Count);
}
}
static IQueryable<T> Where<T, TValue>(
this IQueryable<T> source,
Expression<Func<T, TValue?>> selector,
TValue? value) where TValue : struct
{
var param = Expression.Parameter(typeof (T), "x");
var member = Expression.Invoke(selector, param);
var body = Expression.Equal(
member, Expression.Constant(value, typeof (TValue?)));
var lambda = Expression.Lambda<Func<T,bool>>(body, param);
return source.Where(lambda);
}
Nullable Int comparison in Linq Expression Query
This should do it (if it is LINQ to Entities because it handles comparison to nullables in a somewhat SQL like way)
!x.StatusId.HasValue
|| (x.StatusId!= completeStatus.Id
&& x.StatusId != cancelledStatus.Id)
With other LINQ providers you may need to use x.StatusId.Value
in the value comparisons.
LINQ to SQL - nullable types in where clause
after some more googling, I found the answer:
ref #1
ref #2
int? id = null;
var list = from mt in db.MY_TABLE
where object.Equals(mt.PARENT_KEY, id) //use object.Equals for nullable field
select new { mt.NAME };
This LINQ renders to SQL as follows:
((mt.PARENT_KEY IS NULL) AND (@id IS NULL))
OR ((mt.PARENT_KEY IS NOT NULL) AND (@id IS NOT NULL) AND (mt.PARENT_KEY = @id))
Comparing non nullable `int` to `null` (LINQ)
According to the DefaultIfEmpty method definition, the following code snippets are equivalent:
List<C> list = new List<C>() { }; // empty list
List<C> listDefault = list.DefaultIfEmpty().ToList();
and
List<C> listDefault = new List<C>() { null }; // Since default(C) is null
Thus when you use g.DefaultIfEmpty() you will get a unique cc object which is null, consequently the line:
BoolProp = cc.A_Id != null
will throw a NullReferenceException because cc is null.
At the end it seems that the condition should be:
BoolProp = cc != null
Besides here is a small example which demonstrates the difference through a unit test:
[TestMethod]
public void TestMethod_DefaultifEmpty()
{
ListA = new List<A>()
{
new A { Id=1, Value="111" },
new A { Id=2, Value="222" },
};
ListC = new List<C>()
{
new C { Id=1, A_Id=1 }
};
Assert.AreEqual(2, MyMethod().Count());
}
public List<A> ListA { get; set; }
public List<C> ListC { get; set; }
public IEnumerable<A_DTO> MyMethod() =>
from a in ListA
join c in ListC
on a.Id equals c.A_Id into g
from cc in g.DefaultIfEmpty()
select new A_DTO
{
Id = a.Id,
Value = a.Value,
//BoolProp = cc.A_Id != null
BoolProp = cc != null // replace by previous line to see the difference
};
Linq to SQL: Where clause comparing a NullableDateTime with a SQL datetime null column
I think Object.Equals(row.StartDate, aDateTime)
will do the trick.
There is lots about this on Google. For example, this post.
How to compare nullable decimal in Entity Framework?
Just use Nullable.Equals()
. The following should be sufficient:
return Db.Products.Any(p =>
Nullable.Equals(p.WholeSalePriceVATOut, product.WholeSalePriceVATOut));
When EF converts that into SQL, the relevant part of the SQL will be something like:
WHERE (`Product`.`WholeSalePriceVATOut` = @p__0) OR
((`Product`.`WholeSalePriceVATOut` IS NULL) AND (@p__0 IS NULL))))
Entity Framework - Linq - Comparing Nullable objects - NotSupportedException
Try this:
var t = territory == null;
var orders = context.Orders.Where(x =>
t || (x.Territory != null && x.Territory.Id == territory.Id));
The reason behind this is it tries to translate territory
into a constant in SQL query but of course territory
is not primitive or any equivalent type on server, so it throws the exception. You can however cache a constant boolean outside (but still in the effective scope of checking territory
) and use that constant in the query.
How does comparison operator works with null int?
According to MSDN - it's down the page in the "Operators" section:
When you perform comparisons with nullable types, if the value of one of the nullable types is
null
and the other is not, all comparisons evaluate tofalse
except for!=
So both a > b
and a < b
evaluate to false
since a
is null...
Related Topics
The Current Synchronizationcontext May Not Be Used as a Taskscheduler
Getting the "Diff" Between Two Arrays in C#
Linq Expression to Return Property Value
How to Get the Current User Directory
Export Datatable to Excel with Open Xml Sdk in C#
How to Convert Ienumerable to Observablecollection
Why/When Would It Be Appropriate to Override Tostring
Better Way to Query a Page of Data and Get Total Count in Entity Framework 4.1
Deserializing Xml to Objects in C#
Mapping Composite Keys Using Ef Code First
How to Get the Word Under the Cursor in Windows
How to Do Network Discovery Using Udp Broadcast
How Does Transactionscope Roll Back Transactions
How to Specify My Explicit Type Comparator Inline
What Is the Fastest Way I Can Compare Two Equal-Size Bitmaps to Determine Whether They Are Identical
Detecting Network Connection Speed and Bandwidth Usage in C#
String Interning in .Net Framework - What Are the Benefits and When to Use Interning