CODEWITHSUNDEEP
c#.netlinqentity-frameworkentity-framework-4
tobias
tobias

Reputation: 1590

Entity Framework Union and Except Query On Different Types

i want to User entity with Action's of Site Role's but point is ExtraAction entity,Action data will be filter by ExtraAction entity,

in ExtraAction entity:

if Type property == 1 this to be UNION to Action entity
if Type property == 0 this to be EXCEPT to Action entity

  public class User
    {
        public int Id { get; set; }
        public string Email { get; set; }
        public string UserName { get; set; }
        public string Password { get; set; }

        public ICollection<SiteRole> SiteRoles { get; set; }
        public ICollection<ExtraAction> ExtraActions { get; set; }
    }

    public class SiteRole
    {
        public int Id { get; set; }
        public string Description { get; set; }
        public virtual ICollection<Action> Actions { get; set; }
        public virtual ICollection<User> User { get; set; }
    }

    public class ExtraAction
    {
        public int Id { get; set; }
        public int UserId { get; set; }
        public int ActionId { get; set; }
        public byte Type { get; set; }

        public virtual Action Action { get; set; }
        public virtual User User { get; set; }
    }

    public class Action
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string ActionName { get; set; }
        public string ControllerName { get; set; }
        public ICollection<SiteRole> SiteRoles { get; set; }
        public virtual ICollection<ExtraAction> ExtraActions { get; set; }

    }

Upvotes: 0

Views: 2796

Answers (1)

tobias
tobias

Reputation: 1590

finally my solution is below

 var list = dbContext.Actions.Where(u =>
                u.Roles.SelectMany(r => r.User).Any(su => su.Id == Id)).Select(row => new { Action = row }).
                Union(dbContext.ExtraActions.Where(suea => suea.Type == 1 && suea.UserId == Id).Select(row => new { Action = row.Action })).
                Except(dbContext.ExtraActions.Where(suea => suea.Type == 0 && suea.UserId == Id).Select(row => new { Action = row.Action })).ToList();

Upvotes: 1

Related Questions