CODEWITHSUNDEEP
c#linqentity-framework
chamara
chamara

Reputation: 12709

Conversion error in Entity Framework

I'm new to Entity Framework. I'm trying to retrieve data from the database using a LINQ query and Entity Framework.

IEnumerable<Gate> lstGate = from tbsite in dc.tblSites
                            select new Gate
                            {
                              CalledInAt = DateTime.Parse(tbsite.CalledInAt.ToString(), new CultureInfo("en-GB", false)),
                              RemoteType = tbsite.RemoteType,
                              NoOfRemotes = (tbsite.NoOfRemotes == null) ? 0 : Convert.ToInt32(tbsite.NoOfRemotes),
                              GateType = tbsite.GateType,
                              NoOfRacks = (tbsite.NoOfRacks == null) ? 0 : Convert.ToInt32(tbsite.NoOfRacks),
                            };

My Model:

public class Gate
    {

        public DateTime CalledInAt { get; set; }

        public string RemoteType { get; set; }
        public int NoOfRemotes { get; set; }
        public string GateType { get; set; }
        public int NoOfRacks { get; set; }
        public string ClickToEdit { get; set; }

    }

I'm getting following errors.

"LINQ to Entities does not recognize the method 'System.DateTime Parse(System.String, System.IFormatProvider)' method, and this method cannot be translated into a store expression."} System.SystemException {System.NotSupportedException}

{"LINQ to Entities does not recognize the method 'Int32 ToInt32(System.Object)' method, and this method cannot be translated into a store expression."} System.SystemException {System.NotSupportedException}

Upvotes: 1

Views: 3886

Answers (2)

Jehof
Jehof

Reputation: 35554

As Nilesh already pointed out in his comment

You cannot use .Net function inside LINQ, because this will be converted into an actual SQL. That is the reason it is complaining about the conversion. Example = you cannot use Convert.toInt32 as the SQL Server does not know what this function is.

An easy fix for your problem would be to call ToList(). This will execute the query and populate a list with the results. After that it is possible to use ToString() or Convert.ToInt32 cause the values are all in-memory and you are free to use .NET methods.

IEnumerable<Gate> lstGate = from tbsite in dc.tblSites.ToList()
                            select new Gate
                            {
                              CalledInAt = DateTime.Parse(tbsite.CalledInAt.ToString(), new CultureInfo("en-GB", false)),
                              RemoteType = tbsite.RemoteType,
                              NoOfRemotes = (tbsite.NoOfRemotes == null) ? 0 : Convert.ToInt32(tbsite.NoOfRemotes),
                              GateType = tbsite.GateType,
                              NoOfRacks = (tbsite.NoOfRacks == null) ? 0 : Convert.ToInt32(tbsite.NoOfRacks),
                            };

The call dc.tblSites.ToList() will read all values and rows from the table. If you want to apply sorting or filtering to reduce the amount of read data, then you need to apply the Where() or OrderBy() calls before ToList()

Upvotes: 1

Pedro.The.Kid
Pedro.The.Kid

Reputation: 2078

you are getting that error because the Select part is being sent to the server by EF two things

1) the objects from dc.tblSites can be directly used.

2) if you really want to transform them first get the data from the database then create the object .ToArray() is a good one.

... from tbsite in dc.tblSites.ToArray() ...

Upvotes: 0

Related Questions