MVC razor decimal formatting

Issue:

Trying to get a decimal property of an EF generated model to display more than default 2 decimal place accuracy.

Use N6 as the numeric format string.

myDecimal.ToString("N6");

or

string.Format("{0:N6}", myDecimal);

Source:

http://stackoverflow.com/questions/8412882/c-sharp-show-a-decimal-to-6-decimal-places

The entity cannot be constructed in a LINQ to Entities query

Issue:

I had a problem casting an anonymous type to a type of a ModelView class that I had defined.
The query worked but failed on my conversion to the type PayScheduleCurrencyModelView.

Incorrect:

 var currencyDetail = (from hn in db.Host_Nation
                                      join cur in db.Currencies on hn.Currency_ID equals cur.Currency_ID
                                      where hn.Host_Nation_ID.Equals(country)
                                      select new PayScheduleCurrencyModelView
                                      {
                                          Country_ID = hn.Host_Nation_ID,
                                          Country = hn.Host_Nation_Name,
                                          Currency_Code = cur.Code,
                                          Currency_Description = cur.Description,
                                          Exchange_Rate = cur.Rate
                                      }).AsEnumerable().Select(x => new PayScheduleCurrencyModelView
                                      {
                                          Country_ID = x.Country_ID,
                                          Country = x.Country,
                                          Currency_Code = x.Currency_Code,
                                          Currency_Description = x.Currency_Description,
                                          Exchange_Rate = x.Exchange_Rate
                                      }).FirstOrDefault();


Correct:


 var currencyDetail = (from hn in db.Host_Nation
                                      join cur in db.Currencies on hn.Currency_ID equals cur.Currency_ID
                                      where hn.Host_Nation_ID.Equals(country)
                                      select new 
                                      {
                                          Country_ID = hn.Host_Nation_ID,
                                          Country = hn.Host_Nation_Name,
                                          Currency_Code = cur.Code,
                                          Currency_Description = cur.Description,
                                          Exchange_Rate = cur.Rate
                                      }).AsEnumerable().Select(x => new PayScheduleCurrencyModelView
                                      {
                                          Country_ID = x.Country_ID,
                                          Country = x.Country,
                                          Currency_Code = x.Currency_Code,
                                          Currency_Description = x.Currency_Description,
                                          Exchange_Rate = x.Exchange_Rate
                                      }).FirstOrDefault();


The fix was to drop the implied cast in the linq to sql query.  This cast was causing the EF error.

Source:

1. http://stackoverflow.com/questions/12916080/the-entity-or-complex-type-cannot-be-constructed-in-a-linq-to-entities-query

Unable to cast object of type 'System.Data.Entity.Infrastructure.DbQuery' to type of 'System.Web.Mvc.SelectList'

Problem:

Had a solution using the Entity Framework which required a query modification. 


Solution was:
return new SelectList(db.Offices, "Office_ID", "Office_Name");


Changed too:


public class SecurityGroup
{
public string ADGroupSID { get; set; }
public string ADGroupName { get; set; }
public Guid Office_ID { get; set; }
public string ADUser { get; set; }
}


List<FCJC.Model.SecurityGroup> userOffice = theUser.GroupMembership.FindAll(g => ControllerHelper.GetAllOffices().Contains(g.Office_ID)).ToList();









var test = (from o in db.Offices

             join oa in db.OfficeADGroups on o.Office_ID equals oa.Office_ID
           where oa.ADGroup_SID.Equals(userOffice.Select(uo => uo.ADGroupSID).ToString())
select new
{
o.Office_ID,
o.Office_Name
}).AsEnumerable();
return new SelectList(test, "Office_ID", "Office_Name");


The problem did not arise until it ran.  The query compiled but since we are dealing with LINQ the query did not execute until runtime.  Then I received the following error:

Unable to cast object of type 'System.Data.Entity.Infrastructure.DbQuery`1[<>f__AnonymousType9`2[System.Guid,System.String]]' to type 'System.Web.Mvc.SelectList'

Solution:

The problem was the lack of support from Entity Framework.  It will only support primitive data types.  The query uses anonymous typing until it is executed.  This process then tries to resolve the data type.  This will not support the use of custom data types.  I tried a number of work arounds to include forcing the execution of the query and then applying a cast to the SelectListItem type.


Try 1 - Fail
 var test = (from o in db.Offices
  ().Select(o => o.Office_ID) on oa.Office_ID equals uo.Office_ID
                        select new
                        {
                            o.Office_ID,
                            o.Office_Name
                        }).AsEnumerable()
            .Where(o => o.Office_ID.Equals(userOffice.Select(uo => uo.Office_ID)))
         
            new SelectListItem
            {
                Value = x.Office_ID.ToString(),
                Text = x.Office_Name
            });


This will work in Linq to SQL but is not supported in the current version of Entity Framework (v6).
The inclusion of the userOffice LINQ subquery is not supported in EF which will only support constant values.  That ruled out any sort of variable or collection.


Got it finally!


Solution was to take the LINQ out and do a direct query:


   using (var ctx = new FCJCModel())
            {
                var sql = "select Office_ID,Office_Name,Address_ID,Archive,LastUpdatedDate,LastUpdatedUser from office where Office_ID in (" + ofcGuids.Replace("\"", "'") + ")";
                var kk = ctx.Offices.SqlQuery(sql).ToList();
                return new SelectList(kk, "Office_ID", "Office_Name");
            }

Source:

1. http://stackoverflow.com/questions/15211362/only-primitive-types-or-enumeration-types-are-supported-in-this-context
2. http://www.entityframeworktutorial.net/EntityFramework4.3/raw-sql-query-in-entity-framework.aspx

Unable to create a constant value of type 'System.Object'

Problem:

I had a class created using the EF tools.  It generated a field which was a nullable Guid.  The datatype declaration was:


Guid? Status_ID


Each time I ran my controller code to build out my query.  I ran the code


Guid? ms_ID = Guid.Parse(MilitaryStatus_ID);
predicate = predicate.Or(l => l.MilitaryStatus_ID.Equals(ms_ID));


This would always through the unknown system.object error.  It was driving me crazy.

Solution:

I did not have any issues against data types of Guid which lead me to think about the nullable operator.  This seemed to be the issue so I removed it for testing and was able to run the above code no problems.  Thus realized that the issue was Linq not dealing with the nullable condition of my variable correctly.  Thus the work around was  to use == instead of Equals operator.


Guid? ms_ID = Guid.Parse(MilitaryStatus_ID);
predicate = predicate.Or(l => l.MilitaryStatus_ID == ms_ID);

Source:

http://stackoverflow.com/questions/4592432/linq-query-keeps-throwing-unable-to-create-a-constant-value-of-type-system-obje