LINQ to Dataset WHERE clause NULL reference exception

Question

First, take a look at this thread

http://stackoverflow.com/questions/881225/linq-to-dataset-dbnull-problem-null-reference-exception

The link above provides you how to LEFT JOIN and then SELECT column that have NULL value in it without taking Exception error

My problem is I want to COUNT the column is null in WHERE clause (after my LEFT JOIN, some NULL value pop up in the column), something like this

            int tmp1 = 
            (

            from n in dt_query
            join m in dt_query2 
            on n.Field<string>("VC_CLIENT_MAXID") equals m.Field<string>("VC_CHAT_MAXID") into nm

            from m in nm.DefaultIfEmpty()
            where 

                 //string.IsNullorEmpty(m.Field<string>("VC_CHAT_STAFF"))

                 //DBNull.Value.Equals(m.Field<string>("VC_CHAT_STAFF"))

                 //m.IsNull("VC_CHAT_STAFF")

                 //object.Equals(m.Field<string>("VC_CHAT_STAFF"), xxx) <<< String xxx = null on above

            select n.Field<string>("VC_CLIENT_MAXID")

            ).Count();

the commented lines are the solution I tried but it didn't work for me

Every help would be appreciate

ps. Sorry for my poor edited text, this is my first time on this forum

ps2. Code format edited, it takes me a while.

Answer 1

If what you're asking is how to count the number of records where the joined table is null, then give this a try.

int tmp1 = 
    (
    from n in dt_query
    join m in dt_query2 
    on n.Field<string>("VC_CLIENT_MAXID") 
     equals m.Field<string>("VC_CHAT_MAXID") into nm

    from LeftJoinM in nm.DefaultIfEmpty()
    where 

     LeftJoinM  == null

    select n.Field<string>("VC_CLIENT_MAXID")
    ).Count();

If I misunderstood you, please let me know in a comment and I'll be happy to update.

Answer 2

I have not tested the following code..Hope this should work

int tmp1 = 
        (
        from n in dt_query
        join m in dt_query2 
        on n.Field<string>("VC_CLIENT_MAXID") 
            equals m.Field<string>("VC_CHAT_MAXID") into nm

        from temp in nm.where(t => t.Field<string>("VC_CLIENT_MAXID") ==null).DefaultIfEmpty()


        select VC_CLIENT_MAXID=  (temp== null) ? "" :n.Field<string>("VC_CLIENT_MAXID");
        ).Count();