LINQ to Entities does not recognise the method 'System.Web.Security.MembershipUser GetUser()Method'

Question

I have (well had) a bit of code that would get me my lovely username and populate a session with its contents:

ManagePreferencesDataContext lg = new ManagePreferencesDataContext();
            IEnumerable<tblManagePreference> ol;

            ol = from login in lg.tblManagePreferences
                 where login.Username == Membership.GetUser().ToString()
                 select login;

            if (ol.Count() > 0)
            {
                Session["Sess_MemberID"] = ol.First().MemberID;
                Session["Sess_LocationID"] = ol.First().LocationID;

                lblMemberID.Text = Session["Sess_MemberID"].ToString();
                lblLocationID.Text = Session["Sess_LocationID"].ToString();
            }

            else
            {

Fantastic! That worked fine and dandy. However, I've been instructed to move over to sunny Entity Framework and I'll be honest, we don't get on i.e. I don't know enough about it - I know very little to start with!

Anyway, I have attempted to amend the above code with the following:

VDSORDAL.PDC_VDSOREntities lg = new VDSORDAL.PDC_VDSOREntities();
            IEnumerable<VDSORDAL.tblUserPreference> ol;

            ol = from login in lg.tblUserPreferences
                 where login.Username == Membership.GetUser().UserName
                 select login;

            if (ol.Count() > 0)
            {
                Session["VDS_MemberID"] = ol.First().MemberID;
                Session["VDS_LocationID"] = ol.First().LocationID;
                Session["VDS_Username"] = ol.First().Username;

                lblMemberID.Text = Session["VDS_MemberID"].ToString();
                lblLocationID.Text = Session["VDS_LocationID"].ToString();
            }
            else
            {
            }
          }

However, when I try to run this I receive the error that forms the title of this here question.

So in summary - where am I going wrong.

As alway, many apologies for what is most likely a very simple question.

Answer 1

You're 99% there. The problem is that you're passing a method (GetUsername) into the L2E parser that it doesn't understand. You want to deal with values rather than method calls in this case:

VDSORDAL.PDC_VDSOREntities lg = new VDSORDAL.PDC_VDSOREntities();
            IEnumerable<VDSORDAL.tblUserPreference> ol;
            string userName = Membership.GetUser().UserName; //store the value
            ol = from login in lg.tblUserPreferences
                 where login.Username == userName 
                 select login;

            if (ol.Count() > 0)
            {
                Session["VDS_MemberID"] = ol.First().MemberID;
                Session["VDS_LocationID"] = ol.First().LocationID;
                Session["VDS_Username"] = ol.First().Username;

                lblMemberID.Text = Session["VDS_MemberID"].ToString();
                lblLocationID.Text = Session["VDS_LocationID"].ToString();
            }
            else
            {
            }
          }

This happens because the LINQ to Entities Provider evaluates the expression to build the query. It can't execute the method on the Membership object.

Answer 2

Linq is try to build an SQL statement out of the query you gave it. So, it wants a SQL equilvanent of Membership.GetUser().UserName which doesn't exist.

The simple soultion is to pull it out of the LINQ query:

var name = Membership.GetUser().UserName;

ol = from login in lg.tblUserPreferences 
                 where login.Username == name 
                 select login; 

Note that there are some .NET methods (such as Trim() and Contains()), which have SQL counterparts which LINQ knows about, and can translated into SQL.