Linq Queries

Linq Queries

Join and custom selected columns with some expression,

 var Datas = from ev in db.Events
                            join ea in db.EventAttendees on ev.EventId equals ea.EventId
                            join u in db.Users on ea.UserId equals u.UserId
                           // where ea.UserId == userid
                            select new VMEvent
                            {
                                EventName = ev.EventName,
                                EventAttendeeSubmittedDate = ea.SubmittedDate.ToString(),
                                Place = ev.Place,
                                EventDateString = ev.EventDate.ToString(),
                                EventAttendeeNoofMembers = ea.noofMembers.ToString(),
                                PricePerPerson = ev.PricePerPerson,
                                Total = (ev.PricePerPerson * ea.noofMembers).ToString()
                            };

Left outer join query:

 List<VMEvent> data = new List<VMEvent>();

var result = from ev in db.Events
                    join u in db.Users on ev.UserId equals u.UserId
                    join ea in db.EventAttendees on ev.EventId equals ea.EventId into Inners 
                    from od in Inners.DefaultIfEmpty()
                    where ev.EventDate >= startDate && ev.EventDate <= endDate
                    select new VMEvent
                    {
                        EventName = ev.EventName,
                        EventAttendeeSubmittedDate = ea.SubmittedDate.ToString(),
                        Place = ev.Place,
                        EventDateString = ev.EventDate.ToString(),
                        EventAttendeeNoofMembers = ea.noofMembers.ToString(),
                        PricePerPerson = ev.PricePerPerson,
                        Total = (ev.PricePerPerson * ea.noofMembers).ToString(),
                        DivisionName = u.usertype
                    };

 data = result.Distinct().ToList();