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.