CODEWITHSUNDEEP
c#linq
Minhajul Islam
Minhajul Islam

Reputation: 19

LINQ to Entities does not recognize the method ..., and this method cannot be translated into a store expression

My function is as on lambda expression

public IList<ent_Message> messageDetailsArray(decimal from,decimal to)
        {
            owncibai_ExamEntities db = new owncibai_ExamEntities();
            var details = db.Messages.OrderBy(or=>or.mDate).Where(wh => (wh.userNumber==from && wh.messageTo==to) || (wh.messageTo==from && wh.userNumber==to)).Select(a => new ent_Message
            {
                isRead = a.isRead,
                mDate = a.mDate,
                Message1 = a.Message1,
                messID = a.messID,
                userNumber = a.userNumber,
                messageTo=a.messageTo,
                Name=a.User.First_Name+" "+a.User.Last_Name,
                photo = (db.MessagePhotoes.Where(ph => ph.messID == a.messID).Select(b => new ent_MessagePhoto
                {
                    msgPhoto=b.msgPhoto,
                    srl=b.srl
                })).ToList()
            }).ToList();
            var update = db.Messages.Where(wh => wh.messageTo == from).ToList();
            update.ForEach(a => a.isRead = true);
            db.SaveChanges();
            return details;
        }

It works fine when I remove Photo parameter from list. While I add photo it is giving following error.

LINQ to Entities does not recognize the method 'System.Collections.Generic.List1[Entities.ent_MessagePhoto] ToList[ent_MessagePhoto](System.Collections.Generic.IEnumerable1[Entities.ent_MessagePhoto])' method, and this method cannot be translated into a store expression.

Entity class is as follows

public class ent_Message{
     public decimal messID { get; set; }
        public Nullable<decimal> userNumber { get; set; }
        public Nullable<decimal> messageTo { get; set; }
        public Nullable<System.DateTime> mDate { get; set; }
        public string ip { get; set; }
        public string Message1 { get; set; }
        public Nullable<bool> isRead { get; set; }
        public Nullable<decimal> parentID { get; set; }
        public string Name { get; set; }
        public IList<ent_MessagePhoto> photo { get; set; }
}

I am totally confused where I am wrong in photo...

Thanks in advance

Upvotes: 1

Views: 339

Answers (3)

Minhajul Islam
Minhajul Islam

Reputation: 19

Thanks for your suggestion I resolved this issue. Issue was version only. I was using 5.x and updated to 6.x and working fine.

Upvotes: 0

jdweng
jdweng

Reputation: 34421

ent_MessagePhoto must be in the class definition

public class ent_Message<ent_MessagePhoto>
    {
        public decimal messID { get; set; }
        public Nullable<decimal> userNumber { get; set; }
        public Nullable<decimal> messageTo { get; set; }
        public Nullable<System.DateTime> mDate { get; set; }
        public string ip { get; set; }
        public string Message1 { get; set; }
        public Nullable<bool> isRead { get; set; }
        public Nullable<decimal> parentID { get; set; }
        public string Name { get; set; }
        public IList<ent_MessagePhoto> photo { get; set; }
    }​

Upvotes: 0

Rex M
Rex M

Reputation: 144122

This bit:

photo = (db.MessagePhotoes.Where(ph => ph.messID == a.messID).Select(b => new ent_MessagePhoto
    {
        msgPhoto=b.msgPhoto,
        srl=b.srl
    })).ToList() //<-right here

appears inside your outer Select clause. When the IQueryProvider tries to convert your outer Select statement to valid SQL, it will see an inner Select, which of course can be converted to SQL, but then it will hit the ToList() call and fail, because there is no equivalent in SQL.

If you want to perform some operations in your Select projection that can't be done in SQL, they need to be done against the query result set in-memory on the .NET application side. One common way to do this is to put a ToList() before your select statement - this will be interpreted as "send the Where and OrderBy parts to SQL, bring the full result set back into a List, then perform the Select projection".

Upvotes: 2

Related Questions