Joining table empty result

Marius Vasile

6y1.1kLINQ

I have the following query but when I run it it gaves me error, no data. I have entry in all tables related to user and Roles. What am I doing wrong?

var role = await (from a in _context.UsersData.Where(a => a.Id == id)
join b in _context.AppIdentityRoles on a.Id equals b.UserId
join c in _context.UsersRole on b.RoleId equals c.Id
select new UserDataRole
{
UserId = a.Id,
UserName = a.UserName,
RoleId = c.Id,
RoleName = c.Name
}).ToListAsync();

Data model

public class UserDataClass : IdentityUser
{
public string UserStatus { get; set; }
public virtual ICollection AppUserRoles { get; set; }
}
public class UserRoleClass : IdentityRole
{
public virtual ICollection AppUserRoles { get; set; }
}
public class AppIdentityRole : IdentityUserRole
{
public virtual UserDataClass User { get; set; }
public virtual UserRoleClass Role { get; set; }
}
public class UserDataRole
{
public string UserId { get; set; }
public string UserName { get; set; }
public string RoleId { get; set; }
public string RoleName { get; set; }
}

22 Replies

Know the answer? Post it — somebody with the same question will find it here.

Marius Vasile6y ago
Resolved with
UserDataRole = (from a in _context.UsersData
                            join b in _context.AppIdentityRoles on a.Id equals b.UserId into TempData
                            from c in TempData
                            join d in _context.UsersRole on c.RoleId equals d.Id
                            where a.Id == id
                            select new UserDataRole
                            {
                                UserId = a.Id,
                                UserName = a.UserName,
                                RoleId = c.RoleId,
                                RoleName = d.Name
                            }).ToList();
I used a temp Table to store info for next join and....it works
Marius Vasile6y ago
This works
UserDataClass = await _context.UsersData.FirstOrDefaultAsync(u => u.Id == id);
            AppIdentityRole = await _context.AppIdentityRoles.FirstOrDefaultAsync(u => u.UserId == id);
            var roleUser = _context.UserRoles.Where(u => u.UserId == id)
                                .Select(u => new
                                {
                                    roleId = u.RoleId,
                                }).Single();
            var rol = roleUser.roleId;
            UserRoleClass = await _context.UsersRole.Where(x => x.Id == rol).FirstOrDefaultAsync();
This one doesn't. I know there is something I don't do right but I can't see it
var role = await (from a in _context.UsersData
                              join b in _context.AppIdentityRoles on a.Id equals b.UserId
                              join c in _context.UsersRole on b.RoleId equals c.Id
                              where a.Id == id
                              select new UserDataRole
                              {
                                  UserId = a.Id,
                                  UserName = a.UserName,
                                  RoleId = b.RoleId,
                                  RoleName = c.Name
                              }).ToListAsync();
How do I put the above in Razor page? I don't use MVC
Marius Vasile6y ago
I don't used claims or token. Login by username
Laxmidhar Sahoo6y ago
hi sir good

At login time how to find the user .Which method used like userbyusername or userbyuserId ?

are you useing claim ?

Thanks and Regards

Laxmidhar sahoo
Marius Vasile6y ago
I am already using Identity
Laxmidhar Sahoo6y ago
hi
Follow the link and read the instruction on how to migrate database user to application user

https://travis.io/blog/2015/03/24/migrate-from-aspnet-membership-to-aspnet-identity/
Marius Vasile6y ago
The default from VS 2019, nothing else added
Laxmidhar Sahoo6y ago
hi
At the login
1- Authenticate User by calling SignIn method and migrate the database user to application user
Is there any package you used for security ?
like Microsoft.AspNet.Identity.Owin
Thanks and Regards
Laxmidhar sahoo
Marius Vasile6y ago
I don't understand "instatiated", however, what is to be done then?
Laxmidhar Sahoo6y ago
hi sir
UserDataRole is not instatiated
Thanks and Regards
Laxmidhar sahoo
Marius Vasile6y ago
This is the message. what else is required?
Laxmidhar Sahoo6y ago
hi sir

Try catch can use in core ,Apart from you can use logging
using System;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;

public class Program
{
    public static void Main(string[] args)
    {
        CreateHostBuilder(args).Build().Run();
    }

    public static IHostBuilder CreateHostBuilder(string[] args)
    {
        var builtConfig = new ConfigurationBuilder()
            .AddJsonFile("appsettings.json")
            .AddCommandLine(args)
            .Build();

        Log.Logger = new LoggerConfiguration()
            .WriteTo.Console()
            .WriteTo.File(builtConfig["Logging:FilePath"])
            .CreateLogger();

        try
        {
            return Host.CreateDefaultBuilder(args)
                .ConfigureServices((context, services) =>
                {
                    services.AddRazorPages();
                })
                .ConfigureAppConfiguration((hostingContext, config) =>
                {
                    config.AddConfiguration(builtConfig);
                })
                .ConfigureLogging(logging =>
                {
                    logging.AddSerilog();
                })
                .ConfigureWebHostDefaults(webBuilder =>
                {
                    webBuilder.UseStartup();
                });
        }
        catch (Exception ex)
        {
            Log.Fatal(ex, "Host builder error");

            throw;
        }
        finally
        {
            Log.CloseAndFlush();
        }
    }
}
Thanks and Regards
Laxmidhar sahoo
Marius Vasile6y ago
I haven't used try catch error in .net core, I don't know how, please direct me to relevant reading material
Laxmidhar Sahoo6y ago
hi

call in try catch block and convey the error

Thanks and Regards
Laxmidhar sahoo
Marius Vasile6y ago
Is not working, I have the same error
Laxmidhar Sahoo6y ago
hay

Your code is looking good .Check calling synchronously
Marius Vasile6y ago
I have the same result. the id is coming from asp-route-id="@item.Id", item is UserDataClass. The id is the UserId, I've checked
Laxmidhar Sahoo6y ago
var user = _context.UsersData.Where(a => a.Id == id)

in the above line what is id value you pass , instaded of id pass static value and convet the result
Marius Vasile6y ago
It does, I've checked that. I have one user and two roles assigned to same user. All three tables, aspnetusers, aspnetroles and aspnetuserroles have data
Laxmidhar Sahoo6y ago
hay

I am trying to say whether UsersData, AppIdentityRoles and UsersRole tables have any data according to the condition or not manually can check
Marius Vasile6y ago
Thank you for your reply Laxmidhar,

I don't undestand what I have to do, I tried this before and I have result as expected
UserDataClass = await _context.UsersData.Where(a => a.Id == id).FirstOrDefaultAsync();

            var role = _context.UserRoles.Where(u => u.UserId == id)
                                .Select(u => new
                                {
                                    roleId = u.RoleId,
                                }).Single();
            var rol = role.roleId;
            UserRoleClass = await _context.UsersRole.Where(x => x.Id == rol).ToListAsync();
What I want to do is to get info from three tables into one/multiple rows. I can see data from two tables but I haven't managed to show from three
Laxmidhar Sahoo6y ago
Hay
var user = _context.UsersData.Where(a => a.Id == id) '
                        var idntrols = _context.AppIdentityRoles;
                        var userroles  _context.UsersRole;
Your code is looking good
first check the above variables whether data is commong or not.
If data is available compare them

Thanks and Regards
Laxmidhar sahoo