locked
Display columns from other tables to view RRS feed

  • 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//

    Tuesday, May 26, 2015 9:31 AM

Answers

  • 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
                                        });
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 27, 2015 3:25 AM

All replies

  • 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
    });

    Tuesday, May 26, 2015 10:03 AM
  • 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; }
        }
    }

    Tuesday, May 26, 2015 2:13 PM
  • 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
                                        });
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 27, 2015 3:25 AM