Struggling with Lambda expression (VB .net)

Question

I have a relatively simple thing that I can do easily in SQL, but I'm trying to get used to using Lambda expressions, and having a hard time.

Here is a simple example. Basically I have 2 tables.

tblAction (ActionID, ActionName)
tblAudit (AuditID, ActionID, Deleted)

tblAudit may have an entry regarding tblAction with the Deleted flag set to 1.

All I want to do is select actions where we don't have a Deleted entry in tblAudit. So the SQL statement is:

Select tblAction.* 
From tblAction LEFT JOIN tblAudit on tblAction.ActionID=tblAudit.ActionID
where tblAudit.Deleted <> 1

What would be the equivalent of the above in VB.Net's LINQ? I tried:

Context.Actions.Where(Function(act) Context.Audit
.Where(Function(aud) aud.Deleted=False AndAlso aud.ActionID=act.ActionID)).ToList

But that is really an inner join type scenario where it requires that each entry in tblAction also has an Entry in tblAudit. I am using Entity Framework Code First to do the database mapping. Is there a way to define the mapping in a way where you can do this?

I'm not clear what lambda expressions have to do with this. Perhaps you mean [Linq?](http://msdn.microsoft.com/en-us/vstudio/bb688088) — Robert Harvey, Apr 23 '12 at 17:03
Razi, check out [LINQPad](http://www.linqpad.net/) and [101 LINQ Samples in VB](http://msdn.microsoft.com/en-us/vstudio/bb688088). — Jon Crowell, Apr 23 '12 at 18:32
Group Join will do outer join. A navigation property (Action.Audits) as well. — Gert Arnold, Apr 25 '12 at 06:59

score 1 · Answer 1 · answered Apr 24 '13 at 12:03

You should add

Public Property Audits As DbSet<Audit>

into your action entity class (to register the association between those tables).

Now you can just write what you mean:

(From act in Context.Actions Where Not act.Audits.Any(Function(audit) audit.Deleted)).ToArray

which is equivalent to

Context.Actions.Where(Function(act) Not act.Audits.Any(Function(audit) audit.Deleted)).ToArray

and let the LINQ parser do the hard SQL work.

Struggling with Lambda expression (VB .net)

1 Answers1