views:

1081

answers:

0

I have 2 tables as the primary data;

**Table - Agent**
aid name
-------------
1   a
2   b
3   c
4   d
5   e

**Table - Event**
eid event
-------------
1   aaaa
2   bbbb
5   NULL

SELECT * FROM Agent a LEFT JOIN Event e ON a.aid = e.eid

aid name eid event
-----------------------------
1   a 1 aaaa
2   b 2 bbbb
3   c NULL NULL
4   d NULL NULL
5   e 5 NULL

Here is my problem. I want to translate this query into LINQ syntax

SELECT COUNT(*) FROM Agent a LEFT JOIN Event e ON a.aid = e.eid
WHERE e.event IS NULL

This query returns 3 as a result.

As I search around, I have some solutions in my mind

int tmp1 = (from n in Agent        
join m in Event         
on n.Field<string>("aid") equals m.Field<string>("eid") into nm        
from o in nm.DefaultIfEmpty()
where o == null
).Count();

This LINQ returns 2 as a result (as you can see, the row that missing is the last row because it can provide me only the row that can't be LEFT JOIN), so this is not the solution I want. (But it is nearly the perfect answer for me)

int tmp1 = (from n in Agent        
join m in Event        
on n.Field<string>("aid") equals m.Field<string>("eid") into nm        
from o in nm.DefaultIfEmpty()
where o.Field<string>("event") == null
).Count();

This LINQ returns NULLrefernceException for me, because it can't find a column name "event" on the row which can't be LEFT JOIN.

I choose this row as a sample

aid name eid event
-----------------------------
3   c NULL NULL

I assumes that LINQ see the row that can't be LEFT JOIN as one object like this

n("aid")=3  n("name")=c o=NULL

instead of this

n("aid")=3  n("name")=c o("eid")=NULL o("event")=NULL

So, because of it can't find "event" column, that's why I think I get the NULLexception.

I had solved this problem with 2 times query like this;

var tmp1 =   from n in Agent        
join m in Event       
on n.Field<string>("aid") equals m.Field<string>("eid") into nm        
from o in nm.DefaultIfEmpty()
select new {aid = n.Field<string>("aid"),aname = n.Field<string>("name"), 
eevent = o != null ? o.Field<string>("event") : null };

The first half change all field that can't be LEFT JOIN in e.event to NULL first (and the second half will SELECT all NULL value to COUNT, easily right ?)

int tmp2 = (from s in tmp1
where s.eevent == null
select s).Count();

I get 3 as the answer as I wish. But as you can see, this is so indirect way to solve the problem. I get only the answer I want, not the solution I wish. Now, I will ask for your help how to handle NULL exception in WHERE clause, I think somehow it may be end up like this;

int tmp1 = (from n in Agent        
join m in Event        
on n.Field<string>("aid") equals m.Field<string>("eid") into nm        
from o in nm.DefaultIfEmpty()
where "SOME FUNCTION HERE" == null
).Count();

Any help would be appreciate.