What is the proper way to Join two tables in ASP.NET MVC?

Question

I've been working on a simple Dropbox-like app, to learn some ASP.NET MVC. But even before getting a page working decently I've gotten stuck, and there's so much info out there I can't see the forest for the trees anymore.

My situation:

I want to show on a page all the files in a certain folder belonging to a certain person. I've designed my database beforehand and then generated a Code First EF model from that. The relevant part of the database is below. Note that AspNetUsers is part of MVC5 Identity. Linking Identity to my own tables seems to be the hardest part, since the tables belonging to Identity are implemented in IdentityModels, while my tables are implemented in TADModels.

I've been following various tutorials on this page, but can't seem to find anything that helps me accomplish what I need. I'm basically just trying to execute the following 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;

According to one of the aforementioned tutorials, I'm supposed to be able to do something along the lines of:

namespace TAD.Models
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Data.Entity.Spatial;

    [Table("FileTree")]
    public partial class FileTree
    {
        [Key]
        public string FolderID { get; set; }

        [Required]
        [StringLength(128)]
        public string UserID { get; set; }

        [Required]
        [StringLength(255)]
        public string FolderName { get; set; }

        [Required]
        [StringLength(255)]
        public string FolderPath { get; set; }

        public virtual ICollection<File> Files { get; set;  }

        public virtual ApplicationUser user { get; set; }
    }
}

The Files Collection is supposed to find the files associated with the path, and user is supposed to find the user associated with the path. But during Scaffolding I get errors along the lines of TAD.Models.IdentityUserRole: EntityType 'IdentityUserRole' has no key defined. Define the key for this EntityType'. Which I suppose is happening because of ApplicationUSer user

This question seems very confusing, even to me. I simply don't know what I'm looking for. The tutorials I mentioned present situations too simple for my needs, while other info is much too advanced.

EDIT: This is the File Model:

namespace TAD.Models
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Data.Entity.Spatial;

    public partial class File
    {
        [Key]
        public string FileID { get; set; }

        [Required]
        [StringLength(128)]
        public string UserID { get; set; }

        [Required]
        [StringLength(128)]
        public string FolderID { get; set; }

        [Required]
        [StringLength(255)]
        public string FileName { get; set; }

        public decimal FileSize { get; set; }

        public bool IsPublic { get; set; }
        public virtual ApplicationUser user { get; set; }

        public virtual FileTree folder { get; set; }
    }
}

Answer 1

You're not alone. Data modeling masks so much of the goings on that it can be tricky figuring out what is right and wrong.

There's nothing wrong with using LINQ inner join in the sense that it works and it gets you what you need.

However, adding ICollection to the data model for FileTree describes a permanent relationship that can be observed when reading the code, and used whenever you feel like it (which will probably be often, as they're so closely related). i.e. It not only provides the same functionality as the join, but it explains the relationship to other programmers without needing comments or reading code samples.

To use these kind of relationships, most Object Relational Mappings (Entity Framework and NHibernate being two examples of ORM) require that the models specify a primary key in order for them to hook up the foreign key between the child table in the collection and the parent table that has the collection.

So, long story short, use ICollection, but then also specify a primary key on your model for File.

Answer 2

why do you want to join AspNetUsers you are not selecting any data from AspNetUsers thats just an extra joining.

you need just this

SELECT f.FileName, f.FileSize
FROM Files f
INNER JOIN FileTree ft ON ft.FolderID = f.FolderID
WHERE ft.UserID = @id AND ft.FolderPath = @path;

Your EF LINQ query is going be be something like'

var results = (from F in Files
               join FT in FileTree on F.FolderID equals FT.FolderID
               where FT.UserID == "Your User ID" && FT.FolderPath == "SomePath"
               Select new { F.FileName, F.FileSize}).ToList();

Answer 3

For the 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 here you can see that I have made all my Identity Models have Guid as a primary key type. You can easily change that to int , and I reckon that is the way to go!

If you have to do complicated joins, you should leave EF out of it, because it is simply an ORM. It can't replace SQL. You can however create a lovely SQL join in code and have 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 maybe?

Obviously don't concatenate strings with the +, they are immutable.

So lets do your query:

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. The 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 is a list of anonymous objects holding a FileTree entity and a list of File entities. Based on you current model that is.