Base solution for your next web application
Open Closed

joining organization units assignments to custom table #4089


User avatar
0
carelearning created

Hello,

We are trying to join AbsUsersOrganizationUnits to a custom table called "departments" as below:

public class Department : Entity, IMustHaveOrganizationUnit
    {
        public const int MaximumIdLength = 50;
        public const int MaximumTitleLength = 50;

        public string CustomKey { get; set; }
        public bool Locked { get; set; } = false;
        public long OrganizationUnitId { get; set; }

        public virtual OrganizationUnit OrganizationUnit { get; set; }

        public Department()
        {
        }

        public Department(long organizationUnitId, string cutomKey)
        {
            OrganizationUnitId = organizationUnitId;
            CustomKey = cutomKey;
        }
    }
  • Join the UserOrganizationUnits and OrganizationUnits as the variable "first."
  • Join UserOrganizationUnit and our custom table of "departments" in the variable called "second".
  • Join "first" and "second" as the variable called "third"
  • Return expected result as a dictionary as the variable called "result" If any of these joins use .ToList (really hitting the database) then it throws :

The specified cast from a materialized 'System.Data.Entity.Core.Objects.MaterializedDataRecord' type to the '<>f__AnonymousType142[<>f__AnonymousType122[Abp.Authorization.Users.UserOrganizationUnit,Abp.Organizations.OrganizationUnit],<>f__AnonymousType13`2[Abp.Authorization.Users.UserOrganizationUnit,MyCompanyName.AbpZeroTemplate.Departments.Department]]' type is not valid.

The code that is failing is here:

public async Task&lt;Dictionary&lt;long, string&gt;> GetFirstCustomKeysByUserIdAsync(IReadOnlyList&lt;long&gt; ids)
        {
            var assignments = _userOrganizationUnitRepository
                .GetAll()
                .Where(x => ids.Contains(x.UserId));

            var organizationUnits = _organizationUnitRepository
                .GetAll();

            var departments = _departmentRepository
                                .GetAll();

            var first = assignments
                .Join(organizationUnits,
                    a => a.OrganizationUnitId,
                    ou => ou.Id,
                    (a, ou) => new { Assignments = a, OrganizationUnits = ou });

            var second = assignments
                    .Join(departments,
                    a => a.OrganizationUnitId,
                    d => d.OrganizationUnitId,
                    (p, d) => new { Projection = p, Departments = d });

            var third = first.Join(second,
                f => f.Assignments.OrganizationUnitId,
                s => s.Departments.OrganizationUnitId,
                (f, s) => new { First = f, Second = s });


            var result = await third.ToDictionaryAsync(x => x.First.Assignments.UserId,
                                                 x => x.Second.Departments.CustomKey);

            return result;
        }

Any suggestions would be appreciated. Thank you for your time and effort.


9 Answer(s)
  • User Avatar
    0
    carelearning created

    Another point is that we can do this in Linqpad with the following code:

    var ids = new List<long>{
    	2,
    	3,
    	4,
    	5,
    	6,
    	7,
    	8
    };
    
    var assignments = AbpUserOrganizationUnits
                    .Where(x => ids.Contains(x.UserId)).ToList();
    
    var first = assignments.Join(AbpOrganizationUnits,
                                         a => a.OrganizationUnitId,
                        				 ou => ou.Id,
    				                     (a, ou) => new { Assignments = a, OrganizationUnits = ou });
    
    var second = AbpUserOrganizationUnits
                        .Join(Departments,
                        a => a.OrganizationUnitId,
                        d => d.OrganizationUnitId,
                        (p, d) => new { Projection = p, Departments = d });
    
    var third = first.Join(second,
                    f => f.Assignments.OrganizationUnitId,
                    s => s.Departments.OrganizationUnitId,
                    (f, s) => new { First = f, Second = s });
    
    third.Dump();
    

    The results are attached

  • User Avatar
    0
    carelearning created

    LinqPad (Works)

    var results = AbpUserOrganizationUnits
                    .Join(Departments,
                        a => a.OrganizationUnitId,
                        d => d.OrganizationUnitId,
                        (p, d) => new { Projection = p, Departments = d })
                    .Where(x => ids.Contains(x.Projection.UserId))
                    .ToDictionary(x => x.Projection.UserId,
                                  x => x.Departments.CustomKey);
    

    C# (Errors)

    var results = _userOrganizationUnitRepository
                    .GetAll()
                    .Join(_departmentRepository
                            .GetAll(),
                        a => a.OrganizationUnitId,
                        d => d.OrganizationUnitId,
                        (p, d) => new { Projection = p, Departments = d })
                    .Where(x => ids.Contains(x.Projection.UserId))
                    .ToDictionary(x => x.Projection.UserId,
                                  x => x.Departments.CustomKey);
    

    C# (Works)

    var departments = _departmentRepository.GetAllList();
                var assignments =  _userOrganizationUnitRepository.GetAllList();
    
                return departments.Join(assignments,
                        d => d.OrganizationUnitId,
                        a => a.OrganizationUnitId,
                        (d, a) => new { Department = d, Assignment = a })
                    .ToDictionary(x => x.Assignment.UserId,
                        x => x.Department.CustomKey);
    
  • User Avatar
    0
    ismcagdas created
    Support Team

    Hi,

    Does this work ?

    var results = _userOrganizationUnitRepository
                    .GetAll()
                    .Join(_departmentRepository
                            .GetAll(),
                        a => a.OrganizationUnitId,
                        d => d.OrganizationUnitId,
                        (p, d) => new { Projection = p, Departments = d })
                    .Where(x => ids.Contains(x.Projection.UserId))
                    .ToList()
                    .ToDictionary(x => x.Projection.UserId,
                                  x => x.Departments.CustomKey);
    
  • User Avatar
    0
    carelearning created

    @ismcagdas

    I pasted the code you provided and I still get the error. Please see attached screenshot for details. Also included is a 7-Zip compressed version of the Logs.txt file. Logs.7z

  • User Avatar
    0
    carelearning created

    To defeat the file size limit we sent a message with an attachment to the info email address, which you provided in a prior forum suggestion.

    This email contains a working example solution from Asp.Net Boilerplate that could illustrate the possible bug we have uncovered.

    Thanks for your time.

    Please let us know if you have follow-up questions.

  • User Avatar
    0
    ismcagdas created
    Support Team

    Thanks @careLearning,

    We have gor your email and we will investigate it in a short time.

  • User Avatar
    0
    ismcagdas created
    Support Team

    Hi agian,

    We are still investigating the problem of your linq method query syntax. In the mean time, you can use on of the below solutions.

    //Solution 1
    var query = (from ou in _userOrganizationUnitRepository.GetAll()
                 join dep in _departmentRepository.GetAll() on ou.OrganizationUnitId equals dep.OrganizationUnitId into
                     ouJoined
                 from dep in ouJoined.DefaultIfEmpty()
                 where ids.Contains(ou.UserId)
                 select new
                 {
                     Projection = ou,
                     Departments = dep
                 });
    
    return query.ToDictionary(x => x.Projection.UserId,
         x => x.Departments.CustomKey);
    
    //Solution 2
    //First define a class like this
    public class MyTemProj
    {
        public UserOrganizationUnit Projection { get; set; }
    
        public Department Departments { get; set; }
    }
    
    //Then use it in your query
    return _userOrganizationUnitRepository
        .GetAll()
        .Join(_departmentRepository
                .GetAll(),
            a => a.OrganizationUnitId,
            d => d.OrganizationUnitId,
            (p, d) => new MyTemProj { Projection = p, Departments = d })
        .Where(x => ids.Contains(x.Projection.UserId))
        .ToDictionary(x => x.Projection.UserId,
            x => x.Departments.CustomKey);
    
  • User Avatar
    0
    carelearning created

    Hello,

    Were you able to find out any more information about this issue? Thanks!

  • User Avatar
    0
    ismcagdas created
    Support Team

    Hi @careLearning,

    Unfortunately, we haven't looked at it yet. Did any of my last suggestions work for you ?