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;
}

@polybios - Thanks thats let me gt on a little further. Took out the WHERE for the statement with the COUNT and forced it to a constant to test and it works fine. The SELECT without the count works with the WHERE so it seems the issue is when trying to get the COUNT for records when using the WHERE statement. — John Cogan, Jul 31 '13 at 14:43

Tom van Enckevort · Accepted Answer · 2013-07-31T15:08:49.377

2

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!).

edited Jul 31 '13 at 15:08

answered Jul 31 '13 at 14:51

Tom van Enckevort

4,170
1
25
40

Thanks Torn. Changed code to: this.NumberOfCashSales = sJournalCash.Count(); and that gave me a record count. The verbatum code gave an error due to the "s.Date_Time.Date", took off the ".Date" and it returned 0 but put me in the right direction to fix it. Many thanks for the help. – John Cogan Jul 31 '13 at 15:01
Yes, I suppose the `.Date` property wouldn't work, as that doesn't translate to valid SQL. Will update the code example so it checks that the `Date_Time` column is between a start and end date. – Tom van Enckevort Jul 31 '13 at 15:07

score 1 · Answer 2 · answered Jul 31 '13 at 14:45

1

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.

answered Jul 31 '13 at 14:45

Digbyswift

10,310
4
38
66

Incompatible Data Reader

2 Answers2