locked
Missing tables ADO.NET Enitity Data Model RRS feed

  • Question

  • User714900566 posted

    Hello,

    I have created a database with SQL server Where I have three tables:

    1. UserProfile
      1. Username
      2. UserID <pk>
    2. Bandtable
      1. BandName
      2. BandID <pk>
    3. UserByBand
      1. UserID <fk>
      2. BandID <fk>

    When I load this database in an ADO.NET Enitity Data Model visual studio ignores the UserByBandTable because EF is super fancy and it all coverd this. But what is te easiest way to add users to a band?

    Friday, April 11, 2014 2:20 PM

Answers

  • User-812053960 posted

    The table isn't needed in code thanks to the navigation properties. You just need to decide do you want to group the results by band or by user, e.g. do you want to see the band name with a list of people in it:

    Band A
     - person A
     - person B
     - person C
    
    Band B
     - person C
     - person D
     - person E
    etc.

    or do you want to group by person:

    Person A
     - Band A
    
    Person B
     - band A
    
    Person C
     - band A
     - band B
    
    etc.


    If its by band, you query on your Bandtable model (including users), if its by user you query on UserProfile model (including bands).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 18, 2014 4:52 PM

All replies

  • User422038134 posted

    Did you make a foriegn key reference to tables Band table and UserProfile table from UserByBand table?

    Friday, April 11, 2014 3:02 PM
  • User-812053960 posted

    Your model should have made navigation properties for you (probably names userProfiles for the band class and BandTables). You can either use a band to add the user(s) or use a user to add the band(s)

    e.g.

    Entities db = new Entities();
    
    var user = new UserProfile() {UserID = 1, Username = "John Doe"};
    user.BandTables.Add(new BandTable() { BandID = 1, BandName = "Doe Rae Mes"});
    db.UserProfiles.Add(user);
    db.SaveChanges();

    // or

    var band = new BandTable() { BandID = 1, BandName = "Doe Rae Mes"};
    band.UserProfiles.Add(new UserProfile() {UserID = 1, Username = "John Doe"});
    db.BandTables.Add(band);
    db.SaveChanges();

    Note: that's not tested code, its about right but I am not 100% confident - let intellisense guide you.

    Also, you'll need to make sure there is a primary key on your joining table otherwise it will throw an error. So either add a field to UserByBand or make a primary key of the 2 fields in that table.

    Friday, April 11, 2014 3:29 PM
  • User-1657171777 posted

    You've got a bit of a redundancy between your UserProfile table and your UserByBand table.  Unless a User can be a member to multiple bands, you don't need the UserByBand table  A better schema would be:

    ## UserProfile table
    int UserID (primary key)
    string Username
    int BandID (foreign key to the BandID column of Band Table)
    
    ## BandTable int BandID (primary key) string BandName

    This schema stores the user's current band in the UserProfile table (UserProfile.BandID -> BandTable.BandID)

    To your question, "what is the easiest way to add users to a band?"

    First: you need to have bands in your BandTable, so when you create or edit a User, you'll be able to choose from a list of bands

    Step #1) create new 'Create' view for the BandTable -> 'CreateBand' and wire it up to save a band (action code below)

    [HttpPost]
    public ActionResult CreateBand(BandTable band)
    {
      if (ModelState.IsValid)
      {
        db.BandTable.Add(band);
        db.SaveChanges();
    
        return RedirectToAction("Index");
      }
      else
      {
        ModelState.AddModelError("", "Couldn't save the band.");
    
        return View(band);
      }
    }

    Step #2) add some bands using the CreateBand view

    Step #3) create new 'Create' view for the UserProfile table -> 'CreateUser' and wire it up to save a band.  You'll pretty much use a similarly structured post-action, so I won't repeat that, but I will give you some code to help you with the View:

    Action:

    public ActionResult CreateUser()
    {
      var bands = db.BandTable.ToList();
    
      ViewBag.bandlist = bands;
    
      return View();
    }

    View:

    @model ProjectName.Models.UserProfile
    
    @using (BeginForm())
    {
      <div>Username:</div>
      <div>@Html.TextBoxFor(model => model.Username)</div>
    
      <div>Band</div>
      <div>
        @Html.DropDownListFor(model => model.BandID, new SelectList(ViewBag.bandlist, "BandID", "BandName"))
      </div>
    
      <p>
        <input type="Submit" value="Create" />
      <p>
    }

    And that's pretty much it.  You'll have a CreateUser view that will have a text field for you to enter the Username, and you'll also have a dropdown list that will display all the bands you've created.

    Good luck!

    EDIT::

    If you need to show all members of a specific band, here's the action code:

    public ActionResult DisplayBandMembers(int id)
    {
      var members = db.UserProfile.Where(m => m.BandID == id).ToList();
    
      return View(members);
    }

    where the 'id' parameter is the ID of the band.

    example:

    @Html.ActionLink("Metallica", "DisplayBandMembers", new { id = 5 }) 
    
    // where 5 is the ID of Metallica in your BandTable

    Friday, April 11, 2014 3:45 PM
  • User516921731 posted

    Hi,

    I think in your UserByBand table you don't have any primary key.

    The UserID and BandID are just foreign keys from other tables.

    So, initialize one primary key in your UserByBand tables and it will show in your DataModel.

    Regards

    Friday, April 11, 2014 11:51 PM
  • User714900566 posted

    Alright I was a bit unclear the first time I think but UserID and BandID are both primary keys and in the UserByBand table there are only foreign keys.

    It is a site for my school so yes the users could be at more bands at once.

    If I want to make a page with a list with all the bands and there members I normaly want to see the UserByBand page but when I generate a model from my database this table isn't made. So do I have to make a model where I declare the UserByBand?

    Friday, April 18, 2014 4:44 PM
  • User-812053960 posted

    The table isn't needed in code thanks to the navigation properties. You just need to decide do you want to group the results by band or by user, e.g. do you want to see the band name with a list of people in it:

    Band A
     - person A
     - person B
     - person C
    
    Band B
     - person C
     - person D
     - person E
    etc.

    or do you want to group by person:

    Person A
     - Band A
    
    Person B
     - band A
    
    Person C
     - band A
     - band B
    
    etc.


    If its by band, you query on your Bandtable model (including users), if its by user you query on UserProfile model (including bands).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 18, 2014 4:52 PM