Friday, February 24, 2012

IS NULL queries with LinqDataSource

Hi everyone,

I'm trying to query a database table using LinqDataSource and get the rows 'WHERE ParentId IS NULL'. I set the AutoGenerateWhereClause to True, catch the Selecting event and set ParentId to null like so:

protected void MyLinqDataSource_Selecting(object sender, LinqDataSourceSelectEventArgs e){e.WhereParameters["ParentId"] =null;// this doesn't work - why?}

but this is ignored. I've also tried

 e.WhereParameters["ParentId"] = DBNull.Value;

but this doesn't work either!

Is there a way of doing this?

Thanks!

I just tried with the Northwind database and = null works fine for me:

ASPX

<%@. Page Language="C#" AutoEventWireup="true" CodeFile="NullDataSource.aspx.cs" Inherits="NullDataSource" %
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
DataSourceID="LinqDataSource1">
<Columns>
<asp:BoundField DataField="OrderID" HeaderText="OrderID" ReadOnly="True"
SortExpression="OrderID" />
<asp:BoundField DataField="OrderDate" HeaderText="OrderDate" ReadOnly="True"
SortExpression="OrderDate" />
<asp:BoundField DataField="RequiredDate" HeaderText="RequiredDate"
ReadOnly="True" SortExpression="RequiredDate" />
<asp:BoundField DataField="ShippedDate" HeaderText="ShippedDate"
ReadOnly="True" SortExpression="ShippedDate" />
<asp:BoundField DataField="ShipRegion" HeaderText="ShipRegion" ReadOnly="True"
SortExpression="ShipRegion" />
</Columns>
</asp:GridView>
<asp:LinqDataSource ID="LinqDataSource1" runat="server"
ContextTypeName="NorthwindDataContext"
Select="new (OrderID, OrderDate, RequiredDate, ShippedDate, ShipRegion)" TableName="Orders"
Where="ShipRegion == @.ShipRegion" onselecting="LinqDataSource1_Selecting">
<WhereParameters>
<asp:Parameter Name="ShipRegion" Type="String" />
</WhereParameters>
</asp:LinqDataSource>

</div>
</form>
</body>

ASPX.CS

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

public partial class NullDataSource : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void LinqDataSource1_Selecting(object sender, LinqDataSourceSelectEventArgs e)
{
e.WhereParameters["ShipRegion"] = null;

}
}

|||

I'm still a beginner, but when I write this code I would use "==" instead of "=".. is that correct and if so does it help?

e.WhereParameters["ParentID"] == null;

|||

Mike, thanks for replying

I'm actually using AutoGenerateWhereClause='True' on my LinqDataSource. This is because it's used in a user control that I use in different pages to query different tables that have the same structure. This custom user control raises an event that allows each page to set the Where parameters that it likes.

Have you tried your example with AutoGenerateWhereClause='True' i.e. without specifying a Where clause and WhereParameters declaratively? I suspect that's where the problem may be...

welldone101, '=' is the right syntax to use, not '==', as the line is a statement setting the value of e.WhereParameters["ParentId"] to null. My trouble is that when AutoGenerateWhereClause is set to true, LinqDataSourceshould automatically generate a Where parameter for the query which checks whether the "ParentId" property of the object returned by the query (in this case a table row, with a column named 'ParentId') is null. But instead it ignores it!

All in all, I think there should be a way of using AutoGenerateWhereClause and check for NULL!

|||

ppoulos:

Have you tried your example with AutoGenerateWhereClause='True' i.e. without specifying a Where clause and WhereParameters declaratively? I suspect that's where the problem may be...

I tried this. I couldn't get it to work using null or DBNull.Value. Most frustrating. I tried this:

e.WhereParameters.Add("ShipRegion", null);

etc.

I checked with SQL Profiler, and no WHERE clause was generated at all. I swapped it for a valid string value. That worked fine. I checked the values that were being bound in the RowDataBound event. They were null for the ones I would expect to be null. The documentation is not that great (yet, hopefully), at least, I can't find anything that explains this.

|||

In regards to clearing up "=" vs. "==". Gotcha, Thanks for the clarification! I thought you were querying for null values.

|||

The AutoGenerateWhereClause excludes parameters whose values are null or empty. This allows you to have multiple filters on the page and only apply the ones that are set. You can still use null where parameters if you explicitly declare a Where expression, as such:

<asp:LinqDataSource ID="LinqDataSource1" runat="server"

ContextTypeName="DataContext" TableName="Table" Where="ParentID == @.ParentID">

<WhereParameters>

<asp:Parameter Name="ParentID" Type="String" />

</WhereParameters>

</asp:LinqDataSource>

|||

You can implement your own auto-generated Where logic in the Selecting event, so that it can handle these dynamically added Where parameters.

public void OnSelecting(object sender, LinqDataSourceSelectEventArgs e) {
StringBuilder where = new StringBuilder();
foreach (string key in e.WhereParameters.Keys) {
if (where.Length > 0) {
where.Append(" AND ");
}
where.Append(key);
where.Append(" == @.");
where.Append(key);
}
(sender as LinqDataSourceView).Where = where.ToString();
}

|||

Thanks for that.

At first look, this should work, however when I tried it I got a nasty "Operator '==' incompatible with operand types 'Int32?' and 'Object' " error!

I tried e.WhereParameters["ParentId"] = null as well as e.WhereParameters["ParentId"] = string.Empty. Even tried DBNull.Value.

ParentId is an int32? value - does setting a null value for a parameter work for you?

|||

Yes, that makes sense... parameter values that are null are typed as Object. The Linq to SQL data type is Int32?, but Parameter.Type doesn't have nullable equivalents.

For the case of null parameter values, you should instead be able to check "Key == null" in your Where statement.

No comments:

Post a Comment