Converting an Sql query to LINQ

Question

I am trying to convert the below mentioned SQL query to LINQ query

Qry = "select Announcement, StartDate, Enddate,Annkey from
Announcements ";   Qry = Qry & " where Startdate <= #" & Today & "# " 
Qry = Qry & " and Enddate >= #" & Today & "# "  Qry = Qry + " order by
StartDate, Enddate";

What I have made so far -

public bool Selectdatafromannouncements()
        {


            List<Announcement> lstAnnounce = new List<Announcement>(
                from l in objVaccinationContext.Announcements
                where
                    l.StartDate.Value < DateTime.Today
                    && l.EndDate.Value < DateTime.Today
                orderby l.StartDate, l.EndDate
                select new (l.Announcement1, l.StartDate, l.EndDate, l.AnnouncementID)
                );


            return true;
        }

Getting an error near select new ( as type expected please help me out i am a beginner with LINQ

Answer 1

You don't need a constructor here, for creating the list. The curly braces are used for creating anonymous objects, so instead of declaring lstAnnounce of List<Announcement> type, better use the var keyword.

So you can have this:

var query = from l in objVaccinationContext.Announcements
                  where
                    l.StartDate.Value < DateTime.Today
                    && l.EndDate.Value < DateTime.Today
                orderby l.StartDate, l.EndDate
                select new {l.Announcement1, l.StartDate, l.EndDate, l.AnnouncementID };
var lstAnnounce = query.ToList();

If you need a list of Annoucement objects:

var query = 
                from l in objVaccinationContext.Announcements
                where
                    l.StartDate.Value < DateTime.Today
                    && l.EndDate.Value < DateTime.Today
                orderby l.StartDate, l.EndDate
                select l;
List<Announcement> lstAnnounce = query.ToList();

Answer 2

select new (l.Announcement1, l.StartDate, l.EndDate, l.AnnouncementID)

is wrong

try for Anonymous type:

select new {var1 = l.Announcement1, var2 = l.StartDate, vr3 = l.EndDate, var4 = l.AnnouncementID}

Answer 3

The problem is that you are inserting items of an anonymous type into a list of Announcement when performing select new (l.Announcement1, l.StartDate, l.EndDate, l.AnnouncementID). What you need to do is explicitly create instances of Announcement type:

List<Announcement> lstAnnounce = 
objVaccinationContext.Announcements
                     .OrderBy(l => l.StartDate)
                     .ThenBy(l => l.EndDate)
                     .Where(l => l.StartDate.Value < DateTime.Today &&
                                 l.EndDate.Value < DateTime.Today)
                     .Select(l => new Announcement
                     {
                         Announcement1 = l.Announcement1,
                         AnnouncementId = l.AnnouncementId,
                         EndDate = l.EndDate,
                         StartDate = l.StartDate
                     })
                     .ToList();

Answer 4

If objVaccinationContext.Announcements is a collection of Announcement objects, you don't need the anonymous type or to use the construct of List, you could just do:

List<Announcement> lstAnnounce = (
    from l in objVaccinationContext.Announcements
    where
        l.StartDate.Value < DateTime.Today && 
        l.EndDate.Value < DateTime.Today
    orderby l.StartDate, l.EndDate
    select l)
    .ToList();

Answer 5

In your original query you use

Startdate <= Today  and Enddate >= Today 

whereas your Linq has

l.StartDate.Value < DateTime.Today
&& l.EndDate.Value < DateTime.Today

Assuming your top query is what you are after, does this work for you:

 List<Announcement> lstAnnounce = 
            (from l in objVaccinationContext.Announcements
            where
                l.StartDate.Value <= DateTime.Today
                && l.EndDate.Value >= DateTime.Today
            orderby l.StartDate, l.EndDate
            select new Announcement
                 {
                     Announcement1 = l.Announcement1,
                     StartDate = l.StartDate,
                     EndDate = l.EndDate,
                     AnnouncementId = l.AnnouncementId
                 }).ToList();