EF stored procedure

I am working with EF 4.1 , I have a stored procedure in my database and I map it to the EF model and I add a function import

I need to do something like that :

        MyDataContext db= new MyDataContext();
        var x = from all in db.Allergies
                select new MyAllergy
                {
                    Name = all.Name,
                    Id = all.Id,
                    user = db.GetUser(all.ChangeInfo.CreatedBy).FirstOrDefault()
      开发者_如何学Go          };

but this exception throws :

LINQ to Entities does not recognize the method 'System.Data.Objects.ObjectResult1[System.String] GetUser(System.Nullable1[System.Int64])' method, and this method cannot be translated into a store expression.

Although this works fine : var user = db.GetUser(1).FirstOrDefault()

Edit:

 public class MyAllergy
    {
        public int Id { get; set; }
        public string  Name { get; set; }
        public string  User { get; set; }
    }
public class Allergy
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public ChangeInfo ChangeInfo { get; set; }
    }
public class ChangeInfo
    {
        public long CreatedBy { get; set; }
    }

I had similar issue. What happens that EF creates Expression Tree based on your LINQ expression and it can't translate all.ChangeInfo.CreatedBy, and it will pass it as string, that is why you are getting exception about string/long expression.

What I end up doing, is to get all.ChangeInfo.CreatedBy value upfront. In my case it was easier. In your case you might go with regular foreach loop :(

something like this:

    var myItems = new List<MyAllergy>();

    foreach (var item in db.Allergies)
    {
        var createdBy = item.ChangeInfo.CreatedBy;

        myItems.Add(new MyAlergy {Name = item.Name, Id = item.Id, user = db.GetUser(createdBy).FirstOrDefault()});
    }