How to query IDBSet via linq

Question

I have a table that i am attempting to query in order to create a menu. I am also querying the related tables to pair down result. I have a models project that contains all of my data models. In my Entities file I have

 public IDbSet<Agent> Agents { get; set; }
        public IDbSet<UsersLogin> UsersLogins { get; set; }
        public IDbSet<Role> Roles { get; set; }
        public IDbSet<UserRoleMapping> UserRoleMappings { get; set; }
        public IDbSet<Qualifier> Qualifiers { get; set; }
        public IDbSet<tblMenus> tblMenu { get; set; }
        public IDbSet<tblUserMenuMapping> tblUserMenuMappings { get; set; }
        public IDbSet<tblRoleMenuMapping> tblRoleMenuMappings { get; set; }

In my Interface i have ICollection<tblMenus> GetAllMenus();

Then i have my linq query which pares everything down and returns main menus and child menus.

public ICollection<tblMenus> GetAllMenus()
{
    if (Global.CurrentProfile.UserID == 1)
    {
        return DataAccess.tblMenu.Where(m => !m.IsDeleted).ToList();
    }
    else
    {
        var UserInfo = GetUserInfo();
        UserType = UserInfo.First().UserTypeID;


        var childRoleMenus =



            from menus in DataAccess.tblMenu
            join roleMenus in DataAccess.tblRoleMenuMappings on menus.MenuID equals roleMenus.MenuID
            join userRoles in DataAccess.UserRoleMappings on roleMenus.RoleID equals userRoles.RoleID
            where userRoles.UserID == Global.CurrentProfile.UserID && !menus.IsDeleted
            select menus;


        var userChildMenus = 
            from menus in DataAccess.tblMenu
                             join userMenus in DataAccess.tblUserMenuMappings on menus.MenuID equals userMenus.MenuID
                            where userMenus.UserID == Global.CurrentProfile.UserID
                             select menus;

        var childMenus = childRoleMenus.Union(userChildMenus).ToList();

However when i execute the query in my page it returns this error.

The specified type member 'MenuID' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported

Here are my models.

 public class tblMenus : ModelBase

{
public int MenuID { get; set; }
public string MenuName { get; set; }
public string MenuLink { get; set; }
public Nullable<int> ParentID { get; set; }
public Nullable<bool> IsParent { get; set; }
public string IconImagePath { get; set; }
public Nullable<int> ApplicationID { get; set; }
public int CreatedBy { get; set; }
public System.DateTime CreatedOn { get; set; }
public string UpdatedBy { get; set; }
public Nullable<System.DateTime> UpdatedOn { get; set; }
public bool IsDeleted { get; set; }
public string ProcessedPage { get; set; }
public string MenuTarget { get; set; }
public Nullable<bool> IsEnabled { get; set; }
public string MenuCategory { get; set; }
public int MenuOrder { get; set; }

public virtual ICollection<tblRoleMenuMapping> tblRoleMenuMapping { get; set; }

   public int RoleMenuID { get; set; }
    public int RoleID { get; set; }
    public int MenuID { get; set; }
    public int CreatedBy { get; set; }
    public System.DateTime CreatedOn { get; set; }
    public Nullable<int> UpdatedBy { get; set; }
    public Nullable<System.DateTime> UpdatedOn { get; set; }
    public Nullable<bool> IsDeleted { get; set; }
    public string ProcessedPage { get; set; }
    public string PageAccessibility { get; set; }

    public virtual ICollection<tblMenus> tblMenus { get; set; }

    public virtual ICollection<Role> Role { get; set; }


    public class tblUserMenuMapping : ModelBase
{
    public int UserMenuID { get; set; }
    public int UserID { get; set; }
    public int MenuID { get; set; }
    public Nullable<int> CreatedBy { get; set; }
    public Nullable<System.DateTime> CreatedOn { get; set; }
    public Nullable<int> UpdatedBy { get; set; }
    public Nullable<System.DateTime> UpdatedOn { get; set; }
    public bool IsDeleted { get; set; }

Can you show us the model (and mappings) for `tblUserMenuMappings` and `tblMenu`? Without seeing the mapping, I would guess that `MenuID` is not mapped to a column in the database for one of those two tables. — Noel, Feb 05 '16 at 15:23
Do you have separate mapping configuration classes? Do your underlying tables have MenuID columns? I think you may have a copy/paste issue with your model classes. `tblMenus` has two `MenuID` properties and the class isn't closed. Also, part of `tblUserMenuMapping` seems to be missing. — Noel, Feb 05 '16 at 15:59
Sorry the set in the middle is from tblrolemenumapping. Its actually three different class files and yes in my project they are closed. I wish that was the issue at hand. — Leonardo Wildt, Feb 05 '16 at 16:03
I am not using a mapping configuration class, in my project i am getting the date by calling my data access project (Entities dataAccess){DataAccess = dataAccess;} — Leonardo Wildt, Feb 05 '16 at 16:05

score 1 · Accepted Answer · edited May 23 '17 at 10:28

It's hard to say for sure without seeing the whole of both model classes and your database. Some things to check are:

Verify each respective 'MenuID' column exist in each underlying table. Because you aren't using mapping configurations, you need to make sure the column names follow the convention naming EF expects.
Verify their is a foreign key relationship between the two tables.

From a more general perspective, I would consider using configuration classes so your relationships are explicit and your model is more easily changed from the tables they map to.

Finally, you may see some clues by inspecting the SQL that EF has generated. Use the technique described in this post for any red flags (like EF is looking for a column that doesn't exist):

var result = from x in appEntities
             where x.id = 32
             select x;

var sql = ((System.Data.Objects.ObjectQuery)result).ToTraceString();

How to query IDBSet via linq

1 Answers1