Display columns from other tables to view

Question

User-618475340 posted

I'm displaying the data from a table called gigs, however it contains a couple of foreign keys to tables 'Bands' and 'Venues' so when using this code in my controller

            string user = User.Identity.GetUserId();
            
            var yourgigs = (from g in dbg.gigs
                                   from v in dbg.Venues
                                   from b in dbg.Bands
                                   from ga in g.gigsaccasses
                                   where (ga.Id == user &&
                                   v.venueid == g.venueid &&
                                   b.bandid == g.bandid)
                                   select g);
                           
            return View(yourgigs);

it's displaying bandid and venueid in the view which are meaningless integers.  How would I replace those with what I suppose would be b.bandname, v.venuename and also add v.address1 and v.city?  The SQL statement that does this is

SELECT        bands.bandname, venues.venuename, venues.address1, venues.city, gigs.whatdate, gigs.starttime
FROM            gigs INNER JOIN
                         bands ON gigs.bandid = bands.bandid INNER JOIN
                         gigsaccass ON gigs.gigid = gigsaccass.gigid INNER JOIN
                         dbo.AspNetUsers ON gigsaccass.Id = dbo.AspNetUsers.Id INNER JOIN
                         venues ON gigs.venueid = venues.venueid
						 WHERE dbo.AspNetUsers.Id = //some user//

Answer 1

User1577371250 posted

Hi,

Use Anonymous types as below.

var yourgigs = (from g in dbg.gigs
                                   from v in dbg.Venues
                                   from b in dbg.Bands
                                   from ga in g.gigsaccasses
                                   where (ga.Id == user &&
                                   v.venueid == g.venueid &&
                                   b.bandid == g.bandid)
                                   select new 
                                    {
                                       bandname =  b.bandname,
                                       venuename = v.venuename, 
                                       address1 = v.address1,
                                       city = v.city, 
                                       whatdate = ga.whatdate, 
                                       starttime = ga.starttime
                                    });

Answer 2

User-618475340 posted

It doesn't seem to like the anonymous types as it's throwing this error

The model item passed into the dictionary is of type 'System.Data.Entity.Infrastructure.DbQuery`1[<>f__AnonymousType7`6[System.String,System.String,System.String,System.String,System.DateTime,System.TimeSpan]]', but this dictionary requires a model item of type 'System.Collections.Generic.IEnumerable`1[OnStageTonight_MVC.Models2.gigs]'.

The top part of the view is this

@model IEnumerable<OnStageTonight_MVC.Models2.gigs>

@{
    ViewBag.Title = "Gigs";
}

<h2>Gigs</h2>

<p>
    @Html.ActionLink("Create New", "Create")
</p>
<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.venueid)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.bandid)
        </th>

so it's expecting a type 'gigs.  Here's the model if it helps (I've stripped out some fields for brevity.

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Linq;
using System.Web;

namespace OnStageTonight_MVC.Models2
{
    
    [Table("gigs")]
    public partial class gigs
    {
        public gigs()
        {
            this.gigsaccasses = new HashSet<gigsaccass>();
        }

        [Key]
        public int gigid { get; set; }
        public int venueid { get; set; }
        public int bandid { get; set; }
        [Display(Name="Date")]
        public System.DateTime whatdate { get; set; }
        [Display(Name="Starts at")]
        public System.TimeSpan starttime { get; set; }

        public virtual ICollection<gigsaccass> gigsaccasses { get; set; }
    }

    [Table("gigsaccass")]
    public partial class gigsaccass
    {
        [Key]
        public int gigaccassid { get; set; }
        public int gigid { get; set; }
        public string Id { get; set; }

        public virtual gigs gig { get; set; }
        public virtual AspNetUsers AspNetUser { get; set; }
    }

    [Table("dbo.AspNetUsers")]
    public class AspNetUsers
    {
        [Key]
        public string Id { get; set; }
        public string Email { get; set; }
        public bool EmailConfirmed { get; set; }
        public string PasswordHash { get; set; }
        public string SecurityStamp { get; set; }
        public string PhoneNumber { get; set; }
        public bool PhoneNumberConfirmed { get; set; }
        public bool TwoFactorEnabled { get; set; }
        public Nullable<System.DateTime> LockoutEndDateUtc { get; set; }
        public bool LockoutEnabled { get; set; }
        public int AccessFailedCount { get; set; }
        public string UserName { get; set; }
        public string YourName { get; set; }

        public List<gigsaccass> gigsaccasses { get; set; }
    }

    [Table("venues")]
    public partial class venues
    {
        [Key]
        public int venueid { get; set; }
        [Required]
        [Display(Name = "Venue")]
        public string venuename { get; set; }        
        [Required]
        [Display(Name = "Address")]
        public string address1 { get; set; }
        [Required]
        [Display(Name = "City")]
        public string city { get; set; }
        public List<gigs> venuegigs { get; set; }
    }

    [Table("bands")]
    public class bands
    {
        [Key]
        public int bandid { get; set; }
        [Required]
        [Display(Name = "Name")]
        public string bandname { get; set; }
        public List<gigs> bandgigs { get; set; }
    }

    public partial class gigscontext : DbContext
    {
        public gigscontext()
            : base("DefaultConnection")
        {
        }

        public DbSet<gigs> gigs { get; set; }
        public DbSet<gigsaccass> gigsaccass { get; set; }
        public DbSet<AspNetUsers> AspNetUsers { get; set; }
        public DbSet<venues> Venues { get; set; }
        public DbSet<bands> Bands { get; set; }
    }
}

Answer 3

User1577371250 posted

Hi,

It requires Model gigs.

var yourgigs = (from g in dbg.gigs
                                   from v in dbg.Venues
                                   from b in dbg.Bands
                                   from ga in g.gigsaccasses
                                   where (ga.Id == user &&
                                   v.venueid == g.venueid &&
                                   b.bandid == g.bandid)
                                   select new gigs
                                    {
                                       bandname =  b.bandname,
                                       venuename = v.venuename, 
                                       address1 = v.address1,
                                       city = v.city, 
                                       whatdate = ga.whatdate, 
                                       starttime = ga.starttime
                                    });