CODEWITHSUNDEEP
c#asp.net-mvcentity-frameworklinq
Steve W
Steve W

Reputation: 476

The type of member in type is not compatible with of member in type in EF

In my ASP.NET MVC 5 application, I have an ActionResult in one of my controllers that takes parameters from the client and returns a JSON string. The problem is, I get the error below when I have null values in one or more of the database records. I can clear this error by casting all the null values to an empty string in the view table itself, but I'd rather not keep that as a long-term solution.

System.Data.Entity.Core.MappingException: 'Schema specified is not valid. Errors: EFA.msl(16,12) : error 2019: Member Mapping specified is not valid. The type 'Edm.String[Nullable=False,DefaultValue=,MaxLength=,Unicode=,FixedLength=]' of member 'ScheduledStartTime' in type 'EFAModel.v_DemandList' is not compatible with 'SqlServer.datetime[Nullable=False,DefaultValue=,Precision=3]' of member 'ScheduledStartTime' in type 'EFAModel.Store.v_DemandList'.'

Any recommendations on how should be approaching this scenario?

    [HttpPost]
    public ActionResult GetDemandData()
    {
        //get basic parameters from DataTables
        var draw = Request.Form.GetValues("draw").FirstOrDefault();
        var start = Request.Form.GetValues("start").FirstOrDefault();
        var length = Request.Form.GetValues("length").FirstOrDefault();

        //Find Order Column
        var sortColumn = Request.Form.GetValues("columns[" + Request.Form.GetValues("order[0][column]").FirstOrDefault() + "][name]").FirstOrDefault();
        var sortColumnDir = Request.Form.GetValues("order[0][dir]").FirstOrDefault();

        //find search parameters
        var searchParam = Request.Form.GetValues("search[value]")[0];

        //build return dataset
        int pageSize = length != null ? Convert.ToInt32(length) : 0;
        int skip = start != null ? Convert.ToInt32(start) : 0;
        int recordsTotal = 0;

        //materialize dataset using serch parameters
        EFAEntities efa = new EFAEntities();

        //*** throws error when database table contains null values ***
        var dataSet = (from a in efa.v_DemandList
                       where a.ScheduledStartTime.Contains(searchParam)
                       select a
                       ).Distinct().OrderBy(sortColumn + " " + sortColumnDir);

        recordsTotal = dataSet.Count();
        var data = dataSet.Skip(skip).Take(pageSize).ToList();
        return Json(new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = data }, JsonRequestBehavior.AllowGet);

    }

Sample from View Table:

Database Sample

Upvotes: 1

Views: 752

Answers (2)

TanvirArjel
TanvirArjel

Reputation: 32059

Error message is clear! Its clearly saying that ScheduledStartTime type in efa.v_DemandList is not the same type for ScheduledStartTime in database table. In your model class ScheduledStartTime is string type but in your database table its datatime type.

So change your ScheduledStartTime type from string to DateTime in your DemandList model class.

Then update your query as follows:

var dataSet = (from a in efa.v_DemandList
                   where a.ScheduledStartTime.ToString().Contains(searchParam)
                   select a
                   ).Distinct().OrderBy(sortColumn + " " + sortColumnDir);

Here I am using .ToString() with ScheduledStartTime otherwise you cannot use Contains() because its a DateTime type.

Upvotes: 1

Charles Owen
Charles Owen

Reputation: 2842

Give default values in your action method. If it's in fact null, you'll get the default value. For ints, you need to provide a nullable type.

[HttpPost]
public ActionResult GetDemandData(int? start=0, int? length=0, etc)
{

Upvotes: 0

Related Questions