Incompatible Data Reader

Question

Using MVC 4 with EF 5x and very new to them.

My entities and contexts etc were created automatically in VS 2010 from an existing database but when I try get a result through a raw sql query I am getting the error:

The data reader is incompatible with the specified 'PlatinumModel.Sales_Journal'. A member of the type, 'Line_No', does not have a corresponding column in the data reader with the same name.

The stack exchange solutions related to this error ( The data reader is incompatible . A member does not... ) have suggested that the error is caused when there is a missing column or that the Model edmx needs updating (Which I have done although there was no need as the database has not been altered)

Sales_Journal.cs - Part of the Platinum.tt file

namespace PPoS_MVC_Site.Models
{
    using System;
    using System.Collections.Generic;

    public partial class Sales_Journal
    {
        public int Line_No { get; set; }
        public Nullable<System.DateTime> Date_Time { get; set; }
        public Nullable<byte> Workstation_No { get; set; }
        public Nullable<short> User_No { get; set; }
        public Nullable<int> Trans_No { get; set; }
        public Nullable<int> Invoice_No { get; set; }
        public string Account_No { get; set; }
        public string Product_Code { get; set; }
        public string Department_No { get; set; }
        public Nullable<float> Qty { get; set; }
        public Nullable<float> Pack_Size { get; set; }
        public Nullable<float> Ave_Cost { get; set; }
        public Nullable<float> Sales_Tax { get; set; }
        public Nullable<byte> Tax_Type { get; set; }
        public Nullable<float> Discount_Amt { get; set; }
        public Nullable<float> Dicount_Value { get; set; }
        public Nullable<float> Line_Total { get; set; }
        public Nullable<float> Function_Key { get; set; }
        public Nullable<byte> Location { get; set; }
        public Nullable<bool> Stock_Level { get; set; }
        public Nullable<int> Branch_No { get; set; }
        public Nullable<int> Cashup_No { get; set; }
        public string Extra { get; set; }
        public Nullable<decimal> Table_No { get; set; }
        public Nullable<decimal> Tab_No { get; set; }
        public Nullable<int> Covers { get; set; }
        public Nullable<int> User_Overide { get; set; }
        public string Room_No { get; set; }
        public string Res_No { get; set; }
        public string Instructions { get; set; }
        public string Order_no { get; set; }
        public Nullable<int> Points { get; set; }
        public string Time_Placed { get; set; }
        public Nullable<int> JobCard_No { get; set; }
        public Nullable<int> Quote_No { get; set; }
        public string Serial_No { get; set; }
    }
}

Sales_Journal SQL creation code

CREATE TABLE [dbo].[Sales_Journal](
    [Line_No] [int] IDENTITY(1,1) NOT NULL,
[Date_Time] [datetime] NULL,
[Workstation_No] [tinyint] NULL,
[User_No] [smallint] NULL,
[Trans_No] [int] NULL,
[Invoice_No] [int] NULL,
[Account_No] [nvarchar](16) NULL,
[Product_Code] [nvarchar](25) NULL,
[Department_No] [nvarchar](10) NULL,
[Qty] [real] NULL,
[Pack_Size] [real] NULL,
[Ave_Cost] [real] NULL,
[Sales_Tax] [real] NULL,
[Tax_Type] [tinyint] NULL,
[Discount_Amt] [real] NULL,
[Dicount_Value] [real] NULL,
[Line_Total] [real] NULL,
[Function_Key] [real] NULL,
[Location] [tinyint] NULL,
[Stock_Level] [bit] NULL,
[Branch_No] [int] NULL,
[Cashup_No] [int] NULL,
[Extra] [nvarchar](50) NULL,
[Table_No] [numeric](10, 1) NULL,
[Tab_No] [numeric](10, 1) NULL,
[Covers] [int] NULL,
[User_Overide] [int] NULL,
[Room_No] [char](10) NULL,
[Res_No] [char](10) NULL,
[Instructions] [nvarchar](250) NULL,
[Order_no] [varchar](40) NULL,
[Points] [int] NULL,
[Time_Placed] [varchar](5) NULL,
[JobCard_No] [int] NULL,
[Quote_No] [int] NULL,
[Serial_No] [varchar](25) NULL,
    CONSTRAINT [PK_Sales_Journal] PRIMARY KEY CLUSTERED 
    (
        [Line_No] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

Controller code trying to interact with this

public void calculateBasicDayData() {
    string sSqlCash = "SELECT * FROM Sales_Journal WHERE Function_Key = 9 AND CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, Date_Time))) = '" + this.targetDate.ToString() + "'";
    string sSqlCashCount = "SELECT COUNT(Sales_Journal.Line_No) FROM Sales_Journal WHERE Function_Key = 9 AND CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, Date_Time))) = '" + this.targetDate.ToString() + "'";
    var sJournalCash = platContext.Sales_Journal.SqlQuery(sSqlCash);

    this.NumberOfCashSales = platContext.Sales_Journal.SqlQuery(sSqlCashCount).Count();
    this.TotalCash = this.calculateDayTotalSales(sJournalCash);
}

private decimal calculateDayTotalSales(System.Data.Entity.Infrastructure.DbSqlQuery<Sales_Journal> sj)
{
    decimal cashtotal = 0;

    foreach (var jItem in sj.ToList())
    {
        cashtotal += decimal.Parse(jItem.Line_Total.ToString());
    }

    return cashtotal;
}

Answer 1

You're trying to 'cast' a count to a Sales_Journal object, which does not work:

platContext.Sales_Journal.SqlQuery(sSqlCashCount).Count()

That SQL query returns an integer which it then tries to create a Sales_Journal object from.

Have you tried using LINQ instead? So something like:

this.NumberOfCashSales = 
   platContext.Sales_Journal.Count(s => s.Function_Key == 9 && 
                                        (s.Date_Time != null && 
                                         s.Date_Time >= this.targetDate.Date &&
                                         s.Date_Time < this.targetDate.Date.AddDays(1)));

That gives you strongly typed queries without the need of writing SQL queries in code (which are a lot harder to debug!).

Answer 2

Since Line_No is the primary key, you should be able to replace COUNT(Sales_Journal.Line_No) with COUNT(*) as the query will be optimized anyway.