How to get data from linked Entity Framework table?

Question

C++ / C#

There is the following database

, you need to get the following data {bid.id, bid.text, bid.datetime_created, user_created_name, user_executor_name}
do this

dbContext = new Model1(); модель базы CodeFirst
dbContext.BID.Load();
var bid = dbContext.BID.Local;
bindingSource1.DataSource = bid.ToBindingList();
dataGridView1.DataSource = bindingSource1;

Everything is displayed, but instead of data from the linked table "System.Data.Entity.DynamicProxies. ..." , as far as I understand this is due to LazyLoad.
If you disable LazyLoad like this

dbContext.Configuration.LazyLoadingEnabled = false;

then nothing is output at all.
Prompt what to write that the data was loaded from the connected tables?

Answer 1

Something like this, it is not clear what kind of context you have there.

using(var db = new Model1())
{
  var data = (from bid in db.BID 
             join u_c in db.Users on u_c.id = bid.user_id_created
         join u_e in db.Users on u_e.id = bid.user_id_executor
         select new BIDDto
         {
           id = bid.id, 
           text = bid.text, 
           datetime_created = bid.datetime_created, 
           user_created_name = u_c.Name, 
           user_executor_name = u_e.Name
         }).ToArray();
    /// Дальше помещайте data куда нужно
}

BIDDto is a class with the fields you need.

Answer 2

In your case, the code for Code-First would look something like this:

[Table("Role")]
public class Role
{
    [Column("id"), Key]
    public int Id { get; set; }

    [Column("name"), Required]
    public string Name { get; set; }
}

[Table("User")]
public class User
{
    [Column("id"), Key]
    public int Id { get; set; }

    [Column("name"), Required]
    public string Name { get; set; }

    [Column("password"), Required]
    public string Password { get; set; }

    [Column("role_id"), ForeignKey("Role")]
    public int RoleId { get; set; }

    public virtual Role Role { get; set; }

    [InverseProperty("Creator")]
    public virtual List<Bid> BidsWhereUserAsCreator { get; set; }

    [InverseProperty("Executor")]
    public virtual List<Bid> BidsWhereUserAsExecutor { get; set; }
}

[Table("bid")]
public class Bid
{
    [Column("id"), Key]
    public int Id { get; set; }

    [Column("Text"), Required]
    public string Text { get; set; }

    [Column("DateTime_Created")]
    public DateTime Created { get; set; }

    [Column("user_id_created"), ForeignKey("Creator")]
    public int CreatorId { get; set; }

    [Column("user_id_executor"), ForeignKey("Executor")]
    public int ExecutorId { get; set; }

    public virtual User Creator { get; set; }

    public virtual User Executor { get; set; }
}

public class MyDbContext : DbContext
{
    public virtual DbSet<Role> Roles { get; set; }

    public virtual DbSet<User> Users { get; set; }

    public virtual DbSet<Bid> Bids { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        // убираем каскадное удаление
        // http://stackoverflow.com/questions/17127351/introducing-foreign-key-constraint-may-cause-cycles-or-multiple-cascade-paths
        modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
        modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>();
    }

    public static void Test()
    {
        using (MyDbContext dbContext = new MyDbContext())
        {
            // говорим, что не надо создавать динамически генерируемые прокси-классы
            // (которые System.Data.Entity.DynamicProxies...)
            dbContext.Configuration.ProxyCreationEnabled = false;
            // отключаем ленивую загрузку
            dbContext.Configuration.LazyLoadingEnabled = false;

            // поля класса будут заполнены, но данные навигационных свойств загружены не будут.
            List<Bid> bidsOnly = dbContext.Bids.ToList();

            // поля навигационных свойств можно загрузить через Include
            List<Bid> bidsWithAllInfo = dbContext.Bids.Include(b => b.Creator).Include(b => b.Executor).Include(b => b.Creator.Role).Include(b => b.Executor.Role).ToList();

            // данные для навигационного поля Creator будут загружены автоматически
            IEnumerable<Bid> bids = dbContext.Bids.Include(b => b.Executor).Where(b => b.Creator.RoleId == 1).ToList().Select(
                b => new Bid()
                {
                    Id = b.Id,
                    Text = b.Text,
                    Created = b.Created,
                    Creator = new User() { Name = b.Creator.Name },
                    Executor = new User() { Name = b.Executor.Name }
                });
        }
    }
}

In the case you described, neither lazy loading nor proxy classes prevent you from getting the desired result, and you can not change the default configuration.

Answer 3

Use Include()

Answer 4

You gave a little strange code. Can you see the code of your dbContext?
If you use codfirst, then the tables are linked by setting up the model by creating virtual properties on the model with foreign keys and, if necessary, set up by attributes or the Fluent API. Then the data will be pulled up automatically by dbContext.Bid.UserCreated.Id