Having trouble showing data of database tables using Linq

Question

I'm having a issue in an ASP.NET Core web application where I want to show all the data that is in 2 different database tables (using Linq join).

I have a "General" view model that contains reference to other 2 view models, in this case my "General" view model is UserReadvisorViewModel with references to UserViewModel and ReadvisorViewModel

General view model:

public class UserReadvisorViewModel
{
    public UserViewModel UserViewModel { get; set; } = null!;
    public ReadvisorViewModel ReadvisorViewModel { get; set; } = null!;
}

User view model:

public class UserViewModel
{
    public string Username { get; set; } = null!;
    ...
}

Readvisor view model:

public class ReadvisorViewModel
{ 
    public string Name { get; set; } = null!;
    ...
}

The problem is when trying to make a join of both tables and then assign the result in a list with .ToList() so in the view I can see the results in a foreach

public IActionResult Index()
{
    List<UserReadvisorViewModel> lst;

    using (dbContext db = new dbContext())
    {
        lst = (from a in db.Users
               join b in db.Readvisors
                      on a.Id equals b.Id
               select new UserReadvisorViewModel
                      {
                          UserViewModel =
                              {
                                  Username = a.Username
                                  ...
                              },

                          ReadvisorViewModel =
                              {
                                  Name = b.Name,
                                  ...
                              }
                        }).ToList();
    }

    return View(lst);
}

I get this error

System.ArgumentException: 'must be reducible node'

Answer 1

I tried as below:

Dbcontext:

public class SomeContext : DbContext
{
    public SomeContext(DbContextOptions<SomeContext> options)
        : base(options)
    {

    }
    public DbSet<User> User { get; set; }
    public DbSet<Readvisor> Readvisor { get; set; }
}

in appsettings.json:

"ConnectionStrings": {
    "SomeContext": "Server=(localdb)\\mssqllocaldb;Database=MVCViewComponentProjContext-e1efb976-a7c9-4d8a-bd15-fd573ea70261;Trusted_Connection=True;MultipleActiveResultSets=true"
  }

regist in startup:

public void ConfigureServices(IServiceCollection services)
        {                
            services.AddDbContext<MVCViewComponentProjContext>(options =>
                    options.UseSqlServer(Configuration.GetConnectionString("SomeContext")));
        }

In controller:

 public class UsersController : Controller
    {
        private readonly SomeContext _context;

        public UsersController(SomeContext context)
        {
            _context = context;
        }

        
        public IActionResult Index()
        {   
            
            var vm = (from a in _context.User
                            join b in _context.Readvisor on a.Id equals b.Id
                          select new UserReadvisorViewModel
                          {
                              UserViewModel = new UserViewModel{ Id = a.Id, Username = a.Username },
                              ReadvisorViewModel = new ReadvisorViewModel{Id=b.Id,Name=b.Name }
                          }).ToList();

            return View();
        }
    }

Result:

In your case, The codes below would cause an error(not the error you mentioned) ,you have to create a instance of UserViewModel and UserViewModel

UserViewModel =
                              {
                                  Username = a.Username
                                  ...
                              },

When it comes to why you got the error, I think you need provide more details