I tried from hours searching solution for my issue but no post help me out.
I am try to fetch records from database using Linq (Entity Framework 6.0 / MVC 5.0) with library.
System.Linq.Dynamic;
Complete code below
using (var entities = new vskdbEntities())
{
entity.DataFields = "id, stack_trace";
_list = entities.vsk_error_log
.OrderBy(entity.Order)
.Select("New(" + entity.DataFields + ")")
.Skip(entity.PageSize * entity.PageNumber)
.Take(entity.PageSize)
.Cast<vsk_error_log>()
.ToList();
}
But at runtime this result in error
An exception of type 'System.NotSupportedException' occurred in EntityFramework.SqlServer.dll but was not handled in user code
Additional information: Unable to cast the type 'DynamicClass1' to type 'VideoKit.framework.vsk_error_log'. LINQ to Entities only supports casting EDM primitive or enumeration types.
I know there is casting issue with Select clause but don't know how to fix this casting issue.
Update No 1:
If i try to use code as shown below, it will properly fetch data
using (var entities = new vskdbEntities())
{
var context = ((System.Data.Entity.Infrastructure.IObjectContextAdapter)entities).ObjectContext;
lst = context.CreateObjectSet<vsk_error_log>().AsEnumerable().Select("new (id, stack_trace)").Cast<DynamicClass>().ToList();
}
But data retrieved with type
Generic List of System.Linq.Dynamic.DynamicClass
Instead of vsk_error_log
If i try to cast with proper class vsk_error_log instead of DynamicClass then i got error
Unable to cast object of type 'DynamicClass1' to type 'VideoKit.framework.vsk_error_log'.
Data returned shown in screenshot
To get the entity itself from a dynamic query you have to use the it
keyword, in stead of new
:
using (var entities = new vskdbEntities())
{
_list = entities.vsk_error_log
.OrderBy(entity.Order)
.Select("it") // here "it"
.Skip(entity.PageSize * entity.PageNumber)
.Take(entity.PageSize)
.ToList();
}
From your comment I understand that you're trying to get partly filled vsk_error_log
entities. You can do that using the main part of your query and then convert the DynamicClass
instances into the entities:
var result = new List<vsk_error_log>();
using (var entities = new vskdbEntities())
{
_list = entities.vsk_error_log
.OrderBy(entity.Order)
.Skip(entity.PageSize * entity.PageNumber)
.Take(entity.PageSize)
.Select("new (id, stack_trace)");
foreach(dynamic d in _list)
{
result.Add(new vsk_error_log { id = d.id, stack_trace = d.stack_trace } );
}
}
Update: Complete code which works well with desired output below
var _list = new List<vsk_error_log>();
using (var entities = new vskdbEntities())
{
var context = ((IObjectContextAdapter)entities).ObjectContext;
var lst = context.CreateObjectSet<vsk_error_log>()
.AsEnumerable()
.Select("new(id,stack_trace)")
.Cast<DynamicClass>()
.Skip(entity.PageSize * (entity.PageNumber - 1))
.Take(entity.PageSize)
.ToList();
foreach (dynamic d in lst)
{
_list.Add(new vsk_error_log { id = d.id, stack_trace = d.stack_trace });
}
}
The Cast<type>()
method doesn't know how to cast the dynamic object you have created into the type you have specified so you will have to do it manually:
using (var entities = new vskdbEntities())
{
entity.DataFields = "id, stack_trace";
_list = entities.vsk_error_log
.OrderBy(entity.Order)
.Select("New(" + entity.DataFields + ")")
.Skip(entity.PageSize * entity.PageNumber)
.Take(entity.PageSize)
.Select(x => new vsk_error_log
{
Property1 = x.Property1,
Property2 = x.Property2,
//etc...
})
.ToList();
}