Iterating through LinQ to Entities results

Question

Possible Duplicate:
The entity or complex type ‘ ’ cannot be constructed in a LINQ to Entities query

I asked this question the other day, and actually did not find any answers that worked %100 I'm trying to compare two tables (Incidents and Accounts) and assign Incidents with Matching Customer Id's to Tasks.

   var tasks = (from i in data.Incidents
                     join a in data.Accounts on i.CustomerID equals a.Acct_CID
                     select new
                     {
                         creator_id = a.ID,
                         start_date = i.DateOpened,
                         end_date = i.DateCLosed,
                         product_code = i.ProductCode,
                         install_type = i.InstallType,
                         os = i.OSType,
                         details = i.Description,
                         solution = i.Solution,
                         creator_name = i.TechID,
                         category = i.Title,
                         text = "Ticket for" + " " + i.Name,
                         status_id = 7
                     }).ToArray().Select(x => new Tasks
                     {
                         creator_id = x.creator_id,
                         start_date = x.start_date,
                         end_date = x.end_date,
                         product_code = x.product_code,
                         os = x.os,
                         details = x.details,
                         solution = x.solution,
                         creator_name = x.creator_name,
                         category = x.category,
                         text = x.text,
                         status_id = x.status_id
                     });

   foreach (var item in tasks)
   {
     data.Tasks.Add(item);
   }

Here is the Tasks Class

 public class Tasks
      {
    [Key]
    public int id { get; set; }
    public string text { get; set; }
   // [CheckDateAtribute]
   [Display(Name="Start Date/Time")]
    [DataType(DataType.DateTime)]
    public DateTime start_date { get; set; }
    [DataType(DataType.DateTime)]
    [Display(Name = "End Date/Time")]
    public DateTime end_date { get; set; }
    [Display(Name="Details")]
    [Required]
    public string details { get; set; }
    public int owner_id { get; set; }
    public int creator_id { get; set; }
    public int status_id { get; set; }
    public string reply { get; set; }
    public string creator_name { get; set; }
    public string category { get; set; }
    public string solution { get; set; }
    public string os { get; set; }
    public string install_type { get; set; }
    public string product_code { get; set; }
}

The Incidents Class

   public class Incidents
{
    [Key]
    public int IncidentID { get; set; }
    public string CustomerID { get; set; }
    public string ProductCode { get; set; }
    public string TechID { get; set; }
    public DateTime DateOpened { get; set; }
    public DateTime DateCLosed { get; set; }
    public string Title { get; set; }
    public string Description { get; set; }
    public string Solution { get; set; }
    public string Name { get; set; }
    public string OSType{ get; set; }
    public string InstallType { get; set; }
    public string AddOnSoftware { get; set; }
    public string ScreenShare { get; set; }
}

ANOTHER EDIT: getting a timeout exception now

What is the use of this? You first select into an anonymous object, then you select from that into a Tasks object. Why not directly into Tasks? — Roy Dictus, Oct 17 '12 at 14:25
that's my question this was the code someone with like 40k rep wrote and I figured maybe he knows something I don't — Chazt3n, Oct 17 '12 at 14:27
I doubt I do, but I've gotten so turned around by people "helping" I'm lost, I'm not very experienced with linq to entities and joins. — Chazt3n, Oct 17 '12 at 14:29
He doesn't :-) Just take out the selection into the anonymous and the .AsEnumerable and you'll be fine. Add a .ToList() at the end to put everything into a list in a single action. — Roy Dictus, Oct 17 '12 at 14:30
@Roy LINQ-to-Entites doesn't let you `Select` into an arbitrary class. This code looks to be selecting the required columns out of the database into an anonymous object, then pulling out of the database via `AsEnumerable` before constructing the required class instances on the code side. — Rawling, Oct 17 '12 at 14:34
@RoyDictus because Entity Framework cannot construct a `Tasks` **on the server** (ie in the domain of `IQueryable`), but it *can* construct an anonymous type. Your change would bring the error 'Entity Framework can't create an object of type `Tasks`', or similar. — AakashM, Oct 17 '12 at 14:35
@Rawling, @AakashM You can indeed select into a concrete type, as long as you are not using any methods that are not supported by LINQ to SQL (e.g. `String.Format`, etc). In this example it should work just fine. — jrummell, Oct 17 '12 at 14:37
@Chaz Which specific bit says it doesn't implement `IEnumerable`? — Rawling, Oct 17 '12 at 14:37
@jrummell - the error just edited into the question is exactly the error you get when you try to construct an arbitrary class in a LINQ-to-entities query. — Rawling, Oct 17 '12 at 14:55
and I understand that now, however no questions on SO handle this way and I keep getting redirected to them. I would appreciate some help on the matter of just getting this import to work correctly. If that's not possible I'll thown them into an O(n^2) loop and call it a day ^_^ — Chazt3n, Oct 17 '12 at 14:58
@Rawling there must be something in the `Tasks` class that EF doesn't like. Perhaps the `[Key]` attribute? But you can project into a simple DTO type. — jrummell, Oct 17 '12 at 15:02
@jrummell Fair enough, I'm only really speaking from Google experience... TBH even if it did work I don't think it'd help with 50k records involved anyway. — Rawling, Oct 17 '12 at 15:04
can you please show how this is done? I want to learn this, and I want to use this in the future but I haven't been able to produce working code using Data Transfer Objects in this scenario — Chazt3n, Oct 17 '12 at 15:04

score 2 · Answer 1 · answered Oct 17 '12 at 14:43

2

Your problem is that you're trying to construct a Tasks instance, but you're passing in items as for a collection initializer, rather than as for an object initializer or as constructor parameters.

You need to change

.Select(x => new Tasks{
     x.creator_id,
     x.start_date,
     ... });

into

.Select(x => new Tasks{
     owner_id = x.creator_id,
     start_date = x.start_date,
     ... });

or

.Select(x => new Tasks(
     x.creator_id,
     x.start_date,
     ... ));

depending on how you would normally construct a Tasks.

answered Oct 17 '12 at 14:43

Rawling

49,248
7
89
127

I need to add each of the new tasks (around 50 thousand) into the Tasks table – Chazt3n Oct 17 '12 at 14:50
Well, unedit it. You _need_ the `AsEnumerable` in the middle if you want to create a load of `Tasks` objects. What you then _do_ with those `Tasks` objects - e.g. insert them into another table - is a separate problem. – Rawling Oct 17 '12 at 14:56
@Chazt3n And I'd imagine the "foreach => add" loop you have should work fine! It's just the _query_ part that needs to go back to how it was _before_, just with the `property = ` bits added as I've suggested. – Rawling Oct 17 '12 at 14:58
Ok gotcha, can you provide a code example please? I am dying to see how this actually works as it's quite frustrating. I think the data is too large to do how we are trying now because it keeps timing out – Chazt3n Oct 17 '12 at 15:00
All I can really vouch for is what you needed to change in your original code to avoid the original error you posted. I don't know my way around L2E too much so I can't offer any advice as to selecting 50k records or bulk inserting the same amount. – Rawling Oct 17 '12 at 15:01
I just need these put into a list I can iterate through, right now I am getting a timeout error – Chazt3n Oct 17 '12 at 15:02
Well, use your original code, add the `propertyname =` and slap a `ToList` on the end before your `foreach` loop, see if that helps. – Rawling Oct 17 '12 at 15:05

Furqan Safdar · Answer 2 · 2012-10-17T14:36:14.167

Assuming Tasks class as follows:

class Tasks
{
    public int CreatorID { get; set; } 
    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }
    public int ProductCode { get; set; }
    public string InstallType { get; set; }
    public string OSType { get; set; }
    public string Details { get; set; }
    public string Solution { get; set; }
    public string CreatorName { get; set; }
    public string Category { get; set; }
    public string Text { get; set; }           
    public int StatusID { get; set; }  
}

Usage:

var tasks = (from i in data.Incidents
    join a in data.Accounts on i.CustomerID equals a.Acct_CID
    select new Tasks()
    {                            
        CreatorID = a.ID,
        StartDate = i.DateOpened,
        EndDate = i.DateCLosed,
        ProductCode = i.ProductCode,
        InstallType = i.InstallType,
        OSType = i.OSType,
        Details = i.Description,
        Solution = i.Solution,
        CreatorName = i.TechID,
        Category i.Title,
        Text = "Ticket for" + " " + i.Name,
        StatusID = 7
    });

I don't see how this works, because some properties will not match up, also having compiler errors — Chazt3n, Oct 17 '12 at 14:31
asking for a comma before the semi colon (obviously there shouldn't be a comma there, just leads me to believe something is amiss — Chazt3n, Oct 17 '12 at 14:32
this returns no results, when there are about 50k matches where a.Acct_CID = i.CustomerID — Chazt3n, Oct 17 '12 at 14:46

score 1 · Answer 3 · answered Oct 17 '12 at 14:35

var tasks = 
    from i in data.Incidents
    join a in data.Accounts on i.CustomerID equals a.Acct_CID
    select new Task
    {                            
        creator_id = a.ID,
        start_date = i.DateOpened,
        end_date = i.DateCLosed,
        product_code = i.ProductCode,
        install_type = i.InstallType,
        os = i.OSType,
        details = i.Description,
        solution = i.Solution,
        creator_name = i.TechID,
        category = i.Title,
        text = "Ticket for" + " " + i.Name,
        status_id = 7
    };

Kirill Bestemyanov · Answer 4 · 2012-10-17T14:49:33.247

1

Try this:

var tasks =( from i in data.Incidents
join a in data.Accounts on i.CustomerID equals a.Acct_CID
select new 
{                            
    creator_id = a.ID,
    start_date = i.DateOpened,
    end_date = i.DateCLosed,
    product_code = i.ProductCode,
    install_type = i.InstallType,
    os = i.OSType,
    details = i.Description,
    solution = i.Solution,
    creator_name = i.TechID,
    category = i.Title,
    text = "Ticket for" + " " + i.Name,
    status_id = 7
}).ToArray().Select(x => new Tasks{
    creator_id = x.creator_id,
    start_date = x.start_date,
    end_date = x.end_date,
    product_code = x.product_code,
    os = x.os,
    details = x.details,
    solution = x.solution,
    creator_name = x.creator_name,
    category = x.category,
    text = x.text,
    status_id = x.status_id
});

edited Oct 17 '12 at 14:49

answered Oct 17 '12 at 14:36

Kirill Bestemyanov

11,946
2
24
38

Error 9 Cannot initialize type 'TRX.CRM.Dashboard.Entities.DashBoard.Tasks' with a collection initializer because it does not implement 'System.Collections.IEnumerable' C:\Users\chasten.warwick\Documents\Visual Studio 2010\Projects\TRX.CRM.Dashboard.Web.UI.SVN\TRX.CRM.Dashboard.Web.UI.SVN\TRX.CRM.Dashboard.Web.UI\Controllers\AccountsController.cs 480 60 TRX.CRM.Dashboard.Web.UI – Chazt3n Oct 17 '12 at 14:37
Could you show your Tasks class? – Kirill Bestemyanov Oct 17 '12 at 14:39
The initialization syntax for `Tasks` is wrong. This won't compile. – jrummell Oct 17 '12 at 14:41
I'm trying this now, thanks so much for helping – Chazt3n Oct 17 '12 at 14:56

score 1 · Accepted Answer · answered Oct 17 '12 at 15:15

I think, you are confused with the way we instantiate an anonymous object and an object of a defined type. The query compiles if we remove the Tasks from the Select operator

var tasks = (from i in incidentsList
                       join a in accountsList on i.CustomerID equals a.Acct_CID
                       select new
                       {
                           creator_id = a.ID,
                           start_date = i.DateOpened,
                           end_date = i.DateCLosed,
                           product_code = i.ProductCode,
                           install_type = i.InstallType,
                           os = i.OSType,
                           details = i.Description,
                           solution = i.Solution,
                           creator_name = i.TechID,
                           category = i.Title,
                           text = "Ticket for" + " " + i.Name,
                           status_id = 7
                       }).AsEnumerable().Select(x => new
                     {
                         x.creator_id,
                         x.start_date,
                         x.end_date,
                         x.product_code,
                         x.os,
                         x.details,
                         x.solution,
                         x.creator_name,
                         x.category,
                         x.text,
                         x.status_id
                     });

But, when we instantiate an object of a defined class in the Select operator, values must be assigned to properties of the class. Otherwise, the compiler considers the result of the initialization as a collection. This is the reason why your query is not compiling.

ugh, actually it timed out, anyway to remedy this? "Reading from the stream has failed" — Chazt3n, Oct 17 '12 at 15:43
Please elaborate the issue. I don't understand where did stream come from — S. Ravi Kiran, Oct 17 '12 at 15:54
No worries it's a mysql issue, found a workaround on their website. Thank you for the help I'll take it from here. — Chazt3n, Oct 17 '12 at 15:57

Iterating through LinQ to Entities results

5 Answers5