Search code examples
c#sqlentity-frameworkcrystal-reports

DataSet does not support System.Nullable<>.' Entity Framework


I need my property to allow null, so I declare it as

Nullable<double> Nat_Salary

but when doing so, I get this error

DataSet does not support System.Nullable<>.

This is my code to call the stored procedure and pass table to Crystal Report

if (Report_Number == "2")
{
   var Employee_Data = db.Database.SqlQuery<SR1_Result>("SR1").ToList();
   bs.DataSource = Employee_Data;
}

ReportDocument rpt = new ReportDocument();
rpt.Load(Application.StartupPath + "\\Report\\Arabic\\" + "R" + Report_Number + ".rpt");

rpt.SetDataSource(bs);

This is my class

public partial class SR1_Result
{
    public int EmployeeCode { get; set; }
    public string EmployeeName { get; set; }
    public string JobName { get; set; }
    public System.DateTime Date_Hiring { get; set; }
    public Nullable <double> Nat_Salary { get; set; }
    public string AdministrationName { get; set; }
    public string DepartmentName { get; set; }
    public string BranchName { get; set; }
}

This is the stored procedure:

ALTER PROCEDURE [dbo].[SR1]
AS
BEGIN
    SELECT         
        EmployeeCode,EmployeeName,
        JobName,
        Date_Hiring,
        Nat_Salary,
        AdministrationName,
        DepartmentName,
        BranchName
    FROM            
        Employee_List_Code_Name_Jop_DateHiring s
END 

How can I fix this error without removing the nullable from the Nat_Salary ?


Solution

  • At a guess, because the code you've shown doesn't actually mention or use a DataSet/DataTable at all, Crystal Reports is internally unpacking your List<SR1_Result> to a DataTable, and automagically creating the columns but is blindly passing the Nullable<double> SR1_Result.Nat_Salary to a datatable.Columns.Add("Nat_Salary", typeof(Nat_Salary)) (or its reflective equivalent, and DataTable is objecting to a column being declared as Nullable

    You'll have to fix up the shape of the object you pass in, and most sensibly it'll probably look like a DataTable that you create rather than leaving it to CR to do

    Drop the ToList call and write a loop/some code that creates a datatable and populates it to pass to CR. The column can be declared as having a typeof(double) and also AllowDbNull = true then as you're populating, if the Nat_Salary is null, don't set a value for that column

    Simplistically:

    var dt = new DataTable();
    ...
    dt.Column.Add("Nat_Salary", typeof(double)).AllowDbNull = true;
    ...
    
    foreach(var x in db.Database.SqlQuery<SR1_Result>("SR1"))
      var ro = dt.Rows.NewRow();
      ...
      if(x.Nat_Salary.HasValue)
        ro["Nat_Salary"] = x.Value;
      ...
    }
    

    Or if there is a sensible conversion for Nat_Salary to a non nullable type you can .Select an anonymous type for CR to convert that uses a substitute value for Nat_Salary

    var r = db.Database.SqlQuery<SR1_Result>("SR1").Select(x =>
      ...,
      Nat_Salary = x.Nat_Salary.GetValueOrDefault(0),
      ...
    );
    

    This will change the SR1_Result into an anonymous type that has a Nat_Salary that is no longer a Nullable<double> but just a normal double and CR won't explode when it is internally converting to datatable


    In both these code samples the ... are your other columns in SR1_Result. You're gonna have to list them out unless you want to get into doing the same reflective "look at the object, list its property names and types and turn it into a datatable but apply some custom logic if the property is nullable" process


    Or, perhaps CR already ran into this broken behaviour and put out a version of CR that checks if the property is nullable, and makes a column that allows nulls of the type that Nullable wraps - check for updates/speak to CR (whoever owns them now)