none
Linq2SQL GUIDs and Nulls RRS feed

  • Question

  • Hi All,
    I have a problem I'm working though and I can't seem to figure it out.

    Here's My Categories Table:
    ParentGUID   |   GUID    |   Title

    This is a self referencing table where ParentGUID is either null (root level item) or the GUID of another Category's GUID.

    Here's my code:

            public Guid? CategoryID {
                get {
                    Guid? ret = null;
                    if (Request["CategoryID"] != null ) {
                        try {
                            ret = new Guid(Request["CategoryID"]);
                        } catch { }
                    }

                    return ret;
                }
            }

            private void LoadCategories() {
                var MyCategoryID = CategoryID;

                var Model = new DatabaseDataContext();

                var Searcher = from Item in Model.Categories
                                where  Item.ParentGUID == MyCategoryID
                                orderby Item.Title
                                select Item;
               
                lstChildren.DataSource = Searcher;
                lstChildren.DataBind();

            }

    The problem that I a having is that when MyCategoryID == null, this is the SQL that gets generated:
    exec sp_executesql N'SELECT [t0].[ParentGUID], [t0].[GUID], [t0].[Title]
    FROM [dbo].[Categories] AS [t0]
    WHERE [t0].[ParentGUID] = @p0
    ORDER BY [t0].[Title]',N'@p0 uniqueidentifier',@p0=NULL


    However, this is not good SQL since you can't use the = operate to compare NULL.  What I need to have happen is have Linq "know" whether I passed it a null value or not, if I did, it should generate this code indead:
    exec sp_executesql N'SELECT [t0].[ParentGUID], [t0].[GUID], [t0].[Title]
    FROM [dbo].[Categories] AS [t0]
    WHERE [t0].[ParentGUID] IS NULL
    ORDER BY [t0].[Title]',N'@p0 uniqueidentifier'


    Does anyone have any suggestions on how to make this happen?

    Thanks a ton,
    Tony Valenti
    Thursday, June 19, 2008 3:08 PM

Answers

  • Sorry, I meant to say this:

     

    Change the query to use Equals:

     

      

    Code Snippet
           var Searcher = from Item in Model.Categories
                          where  Object.Equals(Item.ParentGUID, MyCategoryID)
                          orderby Item.Title
                          select Item;

     

     

     

    Thanks,

     

    --Samir

     

    Thursday, June 19, 2008 8:16 PM

All replies

  • Change the query to use Equals:

     

      

    Code Snippet
           var Searcher = from Item in Model.Categories
                          where  Item.ParentGUID.Equals(MyCategoryID)
                          orderby Item.Title
                          select Item;

     

     

     

    Thanks,

     

    --Samir

    Thursday, June 19, 2008 6:31 PM
  • Thanks for the suggestion, but it did not work.  That still generated the following SQL code:

    Code Snippet

    exec sp_executesql N'SELECT [t0].[ParentGUID], [t0].[GUID], [t0].[Title]
    FROM [dbo].[Categories] AS [t0]
    WHERE [t0].[ParentGUID] = @p0
    ORDER BY [t0].[Title]',N'@p0 uniqueidentifier',@p0=NULL



    Do you have any other suggestions?

    -Tony V.
    Thursday, June 19, 2008 7:55 PM
  • Just so you know, I found a workaround but it makes me write really crappy code that I shouldn't have to.  This works:

                var Searcher = (from Item in Model.Categories
                                where (Item.ParentGUID ?? Guid.Empty) == (MyCategoryID ?? Guid.Empty)
                                orderby Item.Title
                                select Item).ToList();


    But it generates poor SQL code:
    exec sp_executesql N'SELECT [t0].[ParentGUID], [t0].[GUID], [t0].[Title]
    FROM [dbo].[Categories] AS [t0]
    WHERE (COALESCE([t0].[ParentGUID],@p0)) = @p1
    ORDER BY [t0].[Title]',N'@p0 uniqueidentifier,@p1 uniqueidentifier',@p0='00000000-0000-0000-0000-000000000000',@p1='00000000-0000-0000-0000-000000000000'

    Does anyone know of a good way to do the Linq search the "right" way?


    Thursday, June 19, 2008 8:06 PM
  • Sorry, I meant to say this:

     

    Change the query to use Equals:

     

      

    Code Snippet
           var Searcher = from Item in Model.Categories
                          where  Object.Equals(Item.ParentGUID, MyCategoryID)
                          orderby Item.Title
                          select Item;

     

     

     

    Thanks,

     

    --Samir

     

    Thursday, June 19, 2008 8:16 PM
  • Awesome!  THank you so much!

    Do you happen to have any details as to why the straight == does not work with this?  I'm glad this works but it seems kinda odd that normal C# syntax generates different results.

    -Tony V.
    Thursday, June 19, 2008 8:37 PM
  • Do you have any idea why your syntax worked but mine didn't?  What's the difference between the two?

    Friday, June 20, 2008 2:29 PM
  • One is Byref the other is byVal

    http://www.c-sharpcorner.com/UploadFile/sachin.nigam/Diffbwreftypeandvalue11152005072944AM/Diffbwreftypeandvalue.aspx


    Friday, October 3, 2008 7:20 PM