Tuesday, December 22, 2009

Implementing UNION with LINQ to Entity

This post uses LINQ to Entity and Set Methods - UNION to select rows from different tables in the database.

What To Do
I have five database tables named Exhibitors, ExhibitorContacts, ExhibitorVessels, ExhibitorDrylands, ExhibitorServices.

An exhibitor(Exhibitors) can have multiple contacts(ExhibitorContacts). A contact can have multiple entries in Vessel(ExhibitorVessels), Dry land(ExhibitorDrylands) or Services(ExhibitorServices).

Now i need to find out all the distinct contacts that have some items in the last three tables.

LINQ to Entity Code is

var cv = (from ev in bts.ExhibitorVessels
                where ev.Exhibitors.ExhibitorID == 3
                 select new { ContactID = ev.ExhibitorContacts.ContactID }
               )
               .Union(
               from ed in bts.ExhibitorDrylands
               where ed.Exhibitors.ExhibitorID == 3
               select new { ContactID = ed.ExhibitorContacts.ContactID}
               )
               .Union(
               from es in bts.ExhibitorServices
               where es.Exhibitors.ExhibitorID == 3
               select new { ContactID = es.ExhibitorContacts.ContactID }
               );


Here bts is my Entity object.