CODEWITHSUNDEEP
c#sqllinqentity-framework
Detilium
Detilium

Reputation: 3026

Only get entries from DB matching the current week

I'm trying to get the entries from the database, where table.Date.WeekDayThing = DateTime.Now.WeekDayThing (refer below for method), but I get the error

LINQ to Entities does not recognize the method 'Int32 GetIso8601WeekOfYear(System.DateTime)' method, and this method cannot be translated into a store expression.

LINQ Query

var dbHours = DAO.Instance.HourRegistration
    .Where(x => x.Login_ID == logId && x.Cust_ID == custId && GetIso8601WeekOfYear(x.Date) == GetIso8601WeekOfYear(DateTime.Now))
    .Include(x => x.Customer)
    .ToList();

Method GetIso8601WeekOfYear(DateTime time)

public int GetIso8601WeekOfYear(DateTime time)
    {
        // Seriously cheat.  If its Monday, Tuesday or Wednesday, then it'll 
        // be the same week# as whatever Thursday, Friday or Saturday are,
        // and we always get those right
        DayOfWeek day = CultureInfo.InvariantCulture.Calendar.GetDayOfWeek(time);
        if (day >= DayOfWeek.Monday && day <= DayOfWeek.Wednesday)
        {
            time = time.AddDays(3);
        }

        // Return the week of our adjusted day
        return CultureInfo.InvariantCulture.Calendar.GetWeekOfYear(time, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday);
    }

Does anyone know how to resolve this issue?

Upvotes: 0

Views: 1955

Answers (2)

bubi
bubi

Reputation: 6501

In LINQ for EF you can use only supported functions that are canonical functions (the only that should be supported by all ef providers) or functions supported by your provider (SQL Server).

In this cases you have 2 different approaches: you can write a stored procedure to solve all the query and then map it to EF or you can explode the query using only the supported functions.

DateTime Canonical functions: https://msdn.microsoft.com/en-us/library/vstudio/bb738563(v=vs.100).aspx

SQL Server EF provider functions: https://msdn.microsoft.com/en-us/library/vstudio/bb399545(v=vs.100).aspx

In your case the right function is DatePart (mixed with some checks).

EDIT

There is always the solution to materialize the query "early" (put a ToList early then work in memory) but this means that you fetch more data than you need.

EDIT2

SQL Server DATEPART has already the way to get the week using ISO standard. Your LINQ query should be this (but I did not try it).

.Where(x => x.Login_ID == logId && x.Cust_ID == custId && SqlFunctions.DatePart("ISO_WEEK", x.Date) == SqlFunctions.DatePart(DateTime.Now))

Reference are the links above and https://msdn.microsoft.com/en-us/library/ms174420.aspx

Upvotes: 0

Horia
Horia

Reputation: 1612

One option is to fetch the data, and apply the week filter after that:

var dbHours = DAO.Instance.HourRegistration
    .Where(x => x.Login_ID == logId && x.Cust_ID == custId)
    .Include(x => x.Customer)
    .ToList()
    .Where(x => GetIso8601WeekOfYear(x.Date) == GetIso8601WeekOfYear(DateTime.Now));

If you think this will bring back too many results you can filter for example to only bring back last 7 days of data which will guarantee the current week is there.

The other option is to re-write your function into EntityFunctions like described here: LINQ to Entities: Age calculation in a LINQ query causes "Method cannot be translated into a store expression"

Upvotes: 2

Related Questions