Showing User Names (stored in ASP.Net Core Identity system), instead of IDs When loading a table

Question

Showing User Names (stored in ASP.Net Core Identity system), instead of IDs When loading a table

asp.net-core c# entity-framework-core

Question

I've having a lot of trouble doing what you would assume a simple task. Changing user IDs to username when showing a table at my web application. This is the data retrieved form the table:(unnecessary information removed)

var data = (from o in _mainDbContext.blog
                        select new blogViewModel
                        {
                            Id = o.id,
                            title = o.title,
                            Editor= o.editorID,

                        } );

editorID is the user ID created by ASP.Net Identity system. I need to load the corresponding user name and place it inside Editor fields for all the entries fetched.

What I have tried so far:

Something like:

var data = (from o in _mainDbContext.blog
join u in _userManager.Users on o.editorID equals u.UserName
                        select new blogViewModel
                        {
                            Id = o.id,
                            title = o.title,
                            Editor= o.editorID,

                        } );

Doesn't work because EF Core doesn't support joining tables from different contexts.

Using foreach like:

foreach (var row in data)
            {
                var user = await _userManager.FindByIdAsync(row.editorID);
                row.Editor= user.UserName;
            }

Doesn't work. It doesn't change the information inside data.

Trying to use raw SQL did not help either. Because FromSql works only on one table and ExecuteSqlCommand does not work with SELECT.

farshad

Popular Answer

Why are your contexts separated ? Why not merge them and create a relation with a navigation property between Users and Blogs.

public class MainDbContext: IdentityDbContext<AppUser>
{
    public DbSet<Blog> Blogs { get; set; }
}

public class Blog 
{
    //Some properties

    public int EditorId { get; set; }

    public AppUser Editor { get; set; }
}

With this you can easily access user's info via the navigation property Editor.

Selmir

View more on Stack Overflow

Edward · Accepted Answer · 8/22/2018 2:18:27 AM

Currently, EF Core don't support query multiple DbContext with one query. You could trace this behavior Query: Throw if second context instance is used in single query #11101.

For a workaround, you may consider convert _userManager.Users to _userManager.Users.ToList() which is a list object.

            var data = from o in _mainDbContext.Blogs
                    join u in _userManager.Users.ToList() on o.EditorID equals u.Id
                    select new BlogViewModel
                    {
                        Id = o.Id,
                        Title = o.Title,
                        Editor = u.UserName
                    };
        var result = data2.ToList();

Showing User Names (stored in ASP.Net Core Identity system), instead of IDs When loading a table

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related