CODEWITHSUNDEEP
c#entity-frameworkentity-framework-6
Gratzy
Gratzy

Reputation: 2908

SQL query to multiple objects using Entity Framework 6

I'm taking the time to learn EF (specifically version 6).

I created two tables in MSSQL and linked up EF6 to the database creating the EF model framework.

Then I created the classes in code. My desire is to have one row pulled with a list of items for "UserDatas" (yes I know it's misspelled).

Consider this code:

public class user
{
    [Key]
    public int pkID;
    public string ForeignCode;
    public string UserName;
    public virtual List<UserData> UserDatas { get; set; }
}

public class UserData
{
    [Key]
    public int pkID;
    public int fkUserID;
    public string ColumnName;
    public string ColumnValue;
}

class Program
{
    static TestData db = new TestData();

    static void Main(string[] args)
    {
        var record = db.tblUsers.Select(x => new { x.UserName, x.pkID }).FirstOrDefault();

        var record2 = db.tblUsers.Include(x => x.tblUserDatas).ToList();

        Console.ReadLine();
    }


}

The first query is just a test to pull the primary record in the tblUsers table.

The second query is where I am attempting to pull all fields related to that user which include things like first name, last name, address, etc...

What happens when I set a break point on the Console.Readline(), I see 5 rows listed for record2. The "user" class is duplicated in each of those rows. I was expecting to see that only listed once with a list of items for "UserDatas".

How can I get this query to come through as I expect with one row containing a list of "UserDatas"?

Again, this is only for learning purposes so don't worry about data and if this is the best way to store it.

Upvotes: 1

Views: 555

Answers (1)

TheGeneral
TheGeneral

Reputation: 81573

It should be as simple as the following (if you don't need the projection/anonymous object) and assuming your entities are configured correctly 

var user = db.tblUsers
             .Include(x => x.UserDatas) // include user data
             .FirstOrDefault(); // select the first user

Some notes,

  1. There is no need to prefix tables with tbl
  2. There is no need to prefix fields with pk, fk
  3. If you used Id, you don't need to specify [key]

Upvotes: 4

Related Questions