Answered by:
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//
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