CODEWITHSUNDEEP
c#linq
mayank
mayank

Reputation: 2665

Convert.ToDateTime in Linq

I want to generate a LINQ statement like this type of SQL statement

SELECT *
FROM   dbo.tbl_Advertisement
WHERE  FileName LIKE '%latest%'
       AND ToDate = (SELECT min(ToDate)
                     FROM   dbo.tbl_Advertisement
                     WHERE  CAST (getdate() AS DATE) <= CAST (Todate AS DATE)
                            AND FromDate = (SELECT max(FromDate)
                                            FROM   dbo.tbl_Advertisement
                                            WHERE  CAST (getdate() AS DATE) >= CAST (FromDate AS DATE)));

I have generated Linq statement as followes:

objAdvList = objAdvList
            .Where(x => x.ToDate == Convert.ToDateTime(objAdvList
                .Where(y => y.FromDate == Convert.ToDateTime(objAdvList.Max(z => z.FromDate)))
                .Select(y => y.FromDate)
                ))
                .Select(x => x)
                .ToList();

Where objAdvList is a List type collection of 

class AdvertisementAccess
{
    public DateTime FromDate { get; set; }
    public DateTime ToDate { get; set; }
    public string FileName { get; set; }
    public string Path { get; set; }
}

But this code give me an error message as followes:

Unable to cast object of type 'WhereSelectListIterator`2[AdvertisementAccess,System.DateTime]' to type 'System.IConvertible'.

Is there anyone to solve my problem?

Regards, Mayank

Upvotes: 1

Views: 2814

Answers (5)

mayank
mayank

Reputation: 2665

Thanks to all of you for your quick and useful replies. By using your help I have generated my Linq statement which is able to fulfill my requirement as replacement of sql statement given below:

SELECT *
FROM   dbo.tbl_Advertisement
WHERE  ToDate = (SELECT min(ToDate)
                     FROM   dbo.tbl_Advertisement
                     WHERE  FromDate = (SELECT max(FromDate)
                                            FROM   dbo.tbl_Advertisement))

My Linq statement is as followes:

objAdvList = objAdvList
            .Where(x => x.ToDate.Date == objAdvList
                .Where(y => y.FromDate.Date == objAdvList.Max(z => z.FromDate.Date))
                .Select(y => y.ToDate.Date).Min()
                )
                .Select(x => x)
                .ToList();

Thanks again, Mayank

Upvotes: 0

Robert McKee
Robert McKee

Reputation: 21487

Your original query looks a bit odd, but this is what it looks like converted to LINQ over Entities, I think:

var objAdvList=objAdvList
     .Where(a=>a.Filename.Contains("latest"))
     .Where(a=>a.ToDate==objAdvList
         .Where(a2=>EntityFunctions.TruncateTime(DateTime.Now)<=EntityFunctions.TruncateTime(a2.FromDate))
         .Where(a2=>EntityFunctions.TruncateTime(a2.FromDate)<=objAdvList
             .Where(a3=>EntityFunctions.TruncateTime(DateTime.Now)>=EntityFunctions.TruncateTime(a3.FromDate))
             .Max(a3=>a3.FromDate))
         .Min(a2=>a2.ToDate))

Upvotes: 0

Gabriele Giuseppini
Gabriele Giuseppini

Reputation: 1581

The argument of your first Convert.ToDateTime() call is an enumeration; make sure you suffix it with a .FirstOrDefault() or Single():

objAdvList = objAdvList
            .Where(x => x.ToDate == Convert.ToDateTime(objAdvList
                .Where(y => y.FromDate == Convert.ToDateTime(objAdvList.Max(z => z.FromDate)))
                .Select(y => y.FromDate)
                .FirstOrDefault()
                ))
                .Select(x => x)
                .ToList();

Upvotes: 1

Jack
Jack

Reputation: 235

Use below query instead of your query.

(objAdvList
            .Where(x => x.ToDate == Convert.ToDateTime(objAdvList
                .Where(y => y.FromDate == Convert.ToDateTime(objAdvList.Max(z => z.FromDate)))
                .Select(y => y.FromDate)
                ))
                .Select(x => x)).Single();

Hope this helps.

Upvotes: 0

David Pfeffer
David Pfeffer

Reputation: 39833

Your inner query results in a set of results. However, Convert.ToDateTime is looking for a single value. You can use either Single, First, or Last to choose a value from this set of values generated by the inner query.

To use Single to retrieve the only result of the inner LINQ query, try the following code:

objAdvList = objAdvList
    .Where(x => x.ToDate == objAdvList
        .Where(y => y.FromDate == Convert.ToDateTime(objAdvList.Max(z => z.FromDate)))
        .Select(y => y.FromDate).Single()
    )
    .ToList();

Alternatively, if you need to pick the first result from a list of possibilities, use First instead:

objAdvList = objAdvList
    .Where(x => x.ToDate == objAdvList
        .Where(y => y.FromDate == Convert.ToDateTime(objAdvList.Max(z => z.FromDate)))
        .Select(y => y.FromDate).First()
    )
    .ToList();

In either case, I removed the .Select(x => x) expression, which doesn't do anything. This expression will merely select all values x and return them untransformed.

I also removed the call to Convert.ToDateTime, as you're already comparing two dates. You do not need to convert a date value to a date.

Upvotes: 4

Related Questions