CODEWITHSUNDEEP
asp.netasp.net-mvclinqasp.net-web-apilinq-to-entities
Explicat
Explicat

Reputation: 1095

LINQ to Entities not supported

In Asp.net Web API I'm having the following model. 

public class User
    {
        [Required, Key]
        public long Id { get; set; }
        public string Name { get; set; }
        public Company Employer { get; set; }
        [Required]
        public DateTime DateAdded { get; set; }
    }

And this is an extract from one of the controllers.

// GET api/Company/5
    public Object GetCompany(int id)
    {
        var query = from user in db.Users
                    join company in db.Companies on user.Employer.Id equals company.Id
                    where company.Id == id
                    orderby user.Name
                    select new { Id = user.Id, Name = user.Name, EmailAddress = user.EmailAddress, Version = user.DateAdded.ToString("dd.MM.yyyy"), Employer = company.Name };
        return query.AsEnumerable<Object>();
    }

What I want is to format the DateAdded property. However, EF throws a NotSupportedException when I try to invoke a method on one of the user's properties or invoke a method of the user. How can I still format the date?

Upvotes: 1

Views: 361

Answers (1)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236208

EF cannot translate user.DateAdded.ToString("dd.MM.yyyy") method into SQL code. You should either move date formatting to client side (with AsEnumerable() or ToList() call) or use SqlFunctions to build date string on server side:

Version = 
   SqlFunctions.DateName("dd", user.DateAdded) + "." +
   SqlFunctions.StringConvert(
       (decimal?)SqlFunctions.DatePart("MM", user.DateAdded)).Trim() + "." +
   SqlFunctions.DateName("yyyy", user.DateAdded)

Upvotes: 1

Related Questions