What is the correct way to combine two tables in ASP.NET MVC?

For Identity Stuff, you can do this:

using System;

using Microsoft.AspNet.Identity.EntityFramework;

/// <summary>
/// The guid role.
/// </summary>
public class GuidRole : IdentityRole<Guid, GuidUserRole>
{
    /// <summary>
    /// Initializes a new instance of the <see cref="GuidRole"/> class.
    /// </summary>
    public GuidRole()
    {
        this.Id = Guid.NewGuid();
    }

    /// <summary>
    /// Initializes a new instance of the <see cref="GuidRole"/> class.
    /// </summary>
    /// <param name="name">
    /// The name.
    /// </param>
    public GuidRole(string name)
        : this()
    {
        this.Name = name;
    }
}

public class GuidUserRole : IdentityUserRole<Guid> { }
public class GuidUserClaim : IdentityUserClaim<Guid> { }
public class GuidUserLogin : IdentityUserLogin<Guid> { }

/// <summary>
/// The user.
/// </summary>
public class User : IdentityUser<Guid, GuidUserLogin, GuidUserRole, GuidUserClaim>
{
    public User()
    {
        this.Id = Guid.NewGuid();
    }
    /// <summary>
    /// Gets or sets the first name.
    /// </summary>
    public string FirstName { get; set; }

    /// <summary>
    /// Gets or sets the last name.
    /// </summary>
    public string LastName { get; set; }
}

      

        
        
        
      

    

So you can see that I made all of my identity models Guid

as the primary key type. You can easily change this to int

and I believe this is the way to go!

If you need to do complex joins, you should leave EF out of it, because it's just an ORM. It cannot replace SQL. However, you can create a fine SQL connection in code and EF execute that query!

using (var context = new ApplicationDbContext())
{
    var result = context.Database.SqlQuery<SomeDtoToHoldTheData>(
                     "SOME LOVELY SQL" + 
                     "SELECT * FROM DATABASE TABLE" +
                     "EVEN PARAMS GO @HERE",
                     new SqlParameter("Here", "My Param"));
}

      

        
        
        
      

    

So, I would suggest looking at this first, perhaps?

Obviously not to concatenate strings with +

, they are immutable.

So let's make your request:

public class FileResultDto
{
    public string FileName {get;set;}
    public decimal FileSize {get;set;}
}

var query = "SELECT f.FileName, f.FileSize" +
            "FROM Files f" +
            "INNER JOIN FileTree ft ON ft.FolderID = f.FolderID" +
            "INNER JOIN AspNetUsers u ON ft.UserID = u.Id" +
            "WHERE u.id = @id AND ft.FolderPath = @path;";

var userIdParam = new SqlParameter("id", userId);
var pathParam = new SqlParameter("path", path);

var result = context.Database.SqlQuery<FileResultDto>(query, userIdParam, pathParam);

      

        
        
        
      

    

If you don't want to go this way. LINQ, I would guess, could go something like this:

var fileTrees = context.FileTrees.Where(f => f.FolderPath == folderPath && f.UserID == userId)
                   .GroupJoin(
                         context.Files
                         ft => ft.FolderID,
                         f => f.FolderID,
                         (fileTree, files) => { fileTree, files });

var users = context.Users.FirstOrDefault(u => u.Id == userId);

      

        
        
        
      

    

So, in fileTrees

there is a list of anonymous objects containing an object FileTree

and a list of objects File

. Based on your current model.