Saturday, December 1, 2018

Querying Database tables using Linq


Join two tables. Check for boolean values in columns. Select multiple columns 

var eventFBQuestions = (from FQ in db.FeedbackQuestions join FT in db.FeedbackTypes on FQ.FeedbackTypeId equals FT.Id where (!FQ.Archived && !FT.Archived && FT.Name == "Event") select new { FQ.Id, FQ.Question }).ToList();

Get the count

var countForOne = (from Fb in db.Feedbacks where (Fb.FeedbackQuestionId == question.Id && Fb.FeedbackScore == 1) select Fb.Id).Count();


Join more than two tables. Alter the select columns by concatenating the select fields

            var sessions = (from s in db.Sessions
                        join t in db.Tracks on s.TrackId equals t.Id
                        join e in db.Events on t.EventId equals e.Id
                        where (!s.Archived && !t.Archived && !e.Archived)
                        select new {s.Id, Name = t.Name + " - " +  s.Name }).ToList();

same thing into a dictionary

            var sessions = (from s in db.Sessions
                        join t in db.Tracks on s.TrackId equals t.Id
                        join e in db.Events on t.EventId equals e.Id
                        where (!s.Archived && !t.Archived && !e.Archived)
                        select new {ID=s.Id.ToString(), NAME = t.Name + " - " +  s.Name }).ToDictionary(dic => dic.ID, dic => dic.NAME);

No comments:

Post a Comment