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