joining organization units assignments to custom table #4089 | Support Center

carelearning created 7 years ago

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)

carelearning created 7 years ago

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

carelearning created 7 years ago

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);

ismcagdas created 7 years ago

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);

0

carelearning created 7 years ago

@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
0

carelearning created 7 years ago

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.
0

ismcagdas created 7 years ago
Support Team

Thanks @careLearning,

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

ismcagdas created 7 years ago

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);

0

carelearning created 7 years ago

Hello,

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

ismcagdas created 7 years ago
Support Team

Hi @careLearning,

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

Have an answer to this question? Log in and write your answer.