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

asp.net-identity asp.net-mvc-5 c# entity-framework-6

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.

enter image description here

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; }
    }
}
1
4
11/10/2014 8:29:26 PM

Popular Answer

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.

2
11/10/2014 8:24:19 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow