c# - Entity Framework 6 不能选择所有列，只能选择一个子集

Question

我正在使用 c# .NET Framework 4.7.2、MySqlData 和 MySqlDataEntityFramework 8.0.22.0。这是我的 DbContext 派生类：

    /// <summary>
/// This is the class that maps the database table to the project's classes.
/// </summary>
[DbConfigurationType(typeof(MySqlEFConfiguration))]
class DatabaseContext : DbContext
{
    /// <summary>
    /// Session table.
    /// </summary>
    public DbSet<Session_table> Sessions { get; set; }
    /// <summary>
    /// Measure table.
    /// </summary>
    public DbSet<Measure_table> Measures { get; set; }


    public DatabaseContext() : base()
    {
        //this.Configuration.LazyLoadingEnabled = false;
    }

    public DatabaseContext(System.Data.Common.DbConnection existingConnection, bool contextOwnConnection)
    : base(existingConnection, contextOwnConnection)
    {

    }


    ///// <summary>
    ///// Measure Table.
    ///// </summary>
    //public DbSet<Measure_table> Measures { get; set; }

    /// <summary>
    /// Override used to map class to database table.
    /// </summary>
    /// <param name="modelBuilder"></param>
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {

        base.OnModelCreating(modelBuilder);

        #region Session Table   
        // session_id is the primary key
        modelBuilder.Entity<Session_table>().HasKey(e => e.session_id);

        // Specify the name of the table 
        modelBuilder.Entity<Session_table>().ToTable("session");

        // Prevent MySql Server to auto-generate the value for primary key
        modelBuilder.Entity<Session_table>().Property(a => a.session_id).HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.None);

        // session_id: Specify name and mark as required
        modelBuilder.Entity<Session_table>().Property(e => e.session_id).HasColumnName("session_id");
        modelBuilder.Entity<Session_table>().Property(e => e.session_id).IsRequired();

        // time_start: Specify name and mark as required
        modelBuilder.Entity<Session_table>().Property(e => e.sessionTimeStart).HasColumnName("time_start");
        modelBuilder.Entity<Session_table>().Property(e => e.sessionTimeStart).IsRequired();

        // Time end: Specify name and mark as required
        modelBuilder.Entity<Session_table>().Property(e => e.sessionTimeEnd).HasColumnName("time_end");
        modelBuilder.Entity<Session_table>().Property(e => e.sessionTimeEnd).IsRequired();

        // Description: Specify name and max length
        modelBuilder.Entity<Session_table>().Property(e => e.description).HasColumnName("description");
        modelBuilder.Entity<Session_table>().Property(e => e.description).HasMaxLength(255);
        #endregion Session Table


        #region Measure Table
        var measureTable = modelBuilder.Entity<Measure_table>();
        measureTable.HasKey(e => e.measure_id);
        measureTable.HasIndex(a => a.session_id);
        measureTable.HasRequired<Session_table>(e => e.session).WithMany().HasForeignKey(e => e.session_id);
        measureTable.ToTable("measure");

        // Measure Id 
        measureTable.Property(e => e.measure_id).HasColumnName("measure_id");
        measureTable.Property(e => e.measure_id).IsRequired();
        // Session Time
        measureTable.Property(e => e.session_time).HasColumnName("session_time");
        measureTable.Property(e => e.session_time).IsRequired();
        // System Time
        measureTable.Property(e => e.system_time).HasColumnName("system_time");
        measureTable.Property(e => e.system_time).IsRequired();
        // SubSystem
        measureTable.Property(e => e.sub_system).HasColumnName("sub_system");
        measureTable.Property(e => e.sub_system).IsRequired();
        // Data Source
        measureTable.Property(e => e.dataSource).HasColumnName("data_source");
        measureTable.Property(e => e.dataSource).IsRequired();
        // Raw
        measureTable.Property(e => e.raw).HasColumnName("raw");
        measureTable.Property(e => e.raw).IsRequired();
        // Calib
        measureTable.Property(e => e.calib).HasColumnName("calib");
        measureTable.Property(e => e.calib).IsRequired();
        // session id
        measureTable.Property(e => e.session_id).HasColumnName("session_id");
        measureTable.Property(e => e.session_id).IsRequired();
        #endregion Measure Table
    }

在这里，我有两个代表数据库表的类：

会话表

/// <summary>
/// Session table
/// </summary>
public class Session_table
{
    /// <summary>
    /// Id of the session, primary key of the table.
    /// </summary>
    public int session_id { get; set; } 
    /// <summary>
    /// Session datetime Start.
    /// </summary>
    public DateTime sessionTimeStart { get; set; } = DateTime.UtcNow;
    /// <summary>
    /// Session DateTime end.
    /// </summary>
    public DateTime sessionTimeEnd { get; set; } = DateTime.UtcNow;
    /// <summary>
    /// Session description.
    /// Default: "No description"
    /// </summary>
    public string description { get; set; } = "No Description";

MEASURE TABLE（具有一对多关系的外键：一个会话 -> 许多度量）

#region Enum
    /// <summary>
    /// Subsystem's type.
    /// </summary>
    public enum SubSystem
    {
        /// <summary>
        /// Zerotype.
        /// </summary>
        ZERO,
        /// <summary>
        /// 1 type.
        /// </summary>
        FIRST,
        /// <summary>
        /// 2 type.
        /// </summary>
        SECOND
    }

    /// <summary>
    /// Source of the measure.
    /// </summary>
    public enum DataSource
    {
        source1,
        source_tbd
    }

    #endregion Enum


    #region Properties
    /// <summary>
    /// Primary key (unique id) for the measure.
    /// Default: 0
    /// </summary>
    public int measure_id { get; set; } = 0;


    /// <summary>
    /// DateTime of the session.
    /// Default: DateTime min value.
    /// </summary>
    public DateTime session_time { get; set; } = DateTime.MinValue.ToUniversalTime();

    /// <summary>
    /// DateTime of the System. 
    /// Default: DateTime min value.
    /// </summary>
    public DateTime system_time { get; set; } = DateTime.MinValue.ToUniversalTime();

    /// <summary>
    /// SubSystem:
    /// </summary>
    public SubSystem sub_system { get; set; } = SubSystem.FIRST;

    /// <summary>
    /// Source of the measure.
    /// </summary>
    public DataSource dataSource { get; set; } = DataSource.source1;

    /// <summary>
    /// Raw value of the measure.
    /// Default: 0
    /// </summary>
    public int raw { get; set; } = 0;

    /// <summary>
    /// Calibrated value of the measure.
    /// Default: 0
    /// </summary>
    public double calib { get; set; } = 0;

    /// <summary>
    /// Foreign Key for One to Many relation.
    /// </summary>
    public virtual Session_table session { get; set; }

    /// <summary>
    /// Foreign key for the session table.
    /// </summary>
    public int session_id { get; set; } = 0;

现在我可以连接到数据库并向两个表中插入一个对象（或列表），我还可以使用

var rows = context.Measures.Select(c => new { c.measure_id, c.raw, c.session_id, c.session.description }).Where(c => c.session_id == 666).ToList();

但是当我尝试获取所有列（整行）时，它在异常“输入字符串格式不正确”中失败，我尝试过：

var rows = from t in context.Measures
                where t.session_id == 666
                select t;

var rows = (from t in context.Measures
                where t.session_id == 666
                select t).AsEnumerable<Measure_table>();

var rows = context.Measures.Where(a => a.session_id == 666).AsEnumerable<Measure_table>();

var rows = from a in context.Measures select a;

var rows = context.Measures;

但这些都不起作用......我肯定错过了一些东西，但我不知道是什么！感谢您的任何建议

我也试过：

        private void selectTest_2(string connet)
    {
        using (MySqlConnection connection = new MySqlConnection(connet))
        {
            try
            {
                connection.Open();
                using (DatabaseContext context = new DatabaseContext(connection, false))
                {
                    foreach (var row in context.Measures)
                    {
                        Console.WriteLine($"VALUE: {row.calib}" + Environment.NewLine);
                        System.Threading.Thread.Sleep(2000);
                    }
                    // Save to the database
                    context.SaveChanges();
                }
            }
            catch (Exception exc)
            {
                Console.WriteLine($"Exception: {exc}");
            }
        }
    }

但导致同样的错误。现在我确定我错过了一些重要的东西......

我找到了一种解决方法......似乎枚举属性是问题，通过使用字符串字段一切正常，目前我正在使用这个“解决方案”但是我怎样才能安全和正确地使用枚举？

        /// <summary>
    /// SubSystem: ZERO, FIRST, SECOND
    /// </summary>
    public SubSystem sub_system_enum { get; set; } = SubSystem.FIRST;
    /// <summary>
    /// String properties for database compatibility
    /// </summary>
    public string sub_system 
    {
        get
        {
            return this.sub_system_enum.ToString();
        }

        set
        {
            this.sub_system_enum = Enum.TryParse<SubSystem>(value, true, out SubSystem val) ? val : default(SubSystem);
        }
    }

和 onModelCreating 覆盖方法：

            // IGnore enums
        measureTable.Ignore(e => e.sub_system_enum);
        measureTable.Ignore(e => e.dataSource_enum);

数据库表： 在此处输入图像描述

标签： c#mysqlentity-frameworklinqinput