none
Query can not be contsructed RRS feed

  • Question

  • Hi everybody,

    I am getting the following error:

    <Error>
    <Message>An error has occurred.</Message>
    <ExceptionMessage>
    The entity or complex type 'Siriusware.Data.ItemsList' cannot be constructed in a LINQ to Entities query.
    </ExceptionMessage>
    <ExceptionType>System.NotSupportedException</ExceptionType>
    <StackTrace>
    at System.Web.Http.ApiController.<InvokeActionWithExceptionFilters>d__1.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult() at System.Web.Http.Dispatcher.HttpControllerDispatcher.<SendAsync>d__0.MoveNext()
    </StackTrace>

    </Error>

    I re-factored some of our lists to use views. In particular, I have the following view:

    create view [dbo].[siriusv_ItemsList] 
    as
    select i.department as Department,
           i.category   as Category,
           i.item       as Item,
           i.departmeId as DepartmeId,
           i.categoryId as CategoryId,
           i.item_Id    as ItemId,
           i.descrip    as Descrip,
           i.hidden     as Hidden
      from dbo.items i 
    ;
    
    GO
    


    The model for this class is generated using Reverse POCO generator. I also added the following 3 extra properties:

        public partial class ItemsList
        {
            public string DepartmentNameHash { get; set; }
            public string CategoryNameHash { get; set; }
            public string ItemNameHash { get; set; }      
        }

    I am getting an error trying to use the following code in my Items Repository class:

       var itemsList = _dbSetList.Where(x => x.Department.Equals(departmentName) && x.Category.Equals(categoryName)).Select(iList => new ItemsList
                {
                    Item = iList.Item,
                    DepartmeId = iList.DepartmeId,
                    Department = iList.Department,
                    ItemId = iList.ItemId,
                    Category = iList.Category,
                    CategoryId = iList.CategoryId,
                    Descrip = iList.Descrip,
                    Hidden = iList.Hidden,
                    DepartmentNameHash = "",
                    CategoryNameHash = "",
                    ItemNameHash = ""
                }); 
    
                if (excludeHidden)
                {
                    itemsList = itemsList.Where(i => i.Hidden == false);
                }
    
                // Parameter sortType is set when we are getting items for 
                // the DCI tree view. Otherwise, it will just be NotSpecified.           
    
                itemsList = SetItemSortOrder(itemsList, sortType);
    
                // Execute the query in the database so we can hash the
                // department, category and item names.
    
                foreach (ItemsList item in itemsList)
                {
                    if (item != null)
                    {
                        item.DepartmentNameHash = item.Department.ToBase64UrlEncoded();
                        item.CategoryNameHash = item.Category.ToBase64UrlEncoded();
                        item.ItemNameHash = item.Item.ToBase64UrlEncoded();
                    }
                }
    
                return itemsList;           
            }

    I've changed the code to add Select(iList=>New ...) to introduce these 3 extra properties explicitly (as I originally was getting the errors in SQL about not having such columns).

    Now it seems like LINQ can not even figure out the query to generate (when I try to trace the code, I do not see the query text as I used to see originally before my change).

    What should I do to fix the problem?

    In short, I am using SQL view and I also want to add 3 extra properties to the class to populate in run-time.

    Thanks a lot in advance.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, February 5, 2015 9:24 PM

Answers

  • The solution was much easier - strange that I didn't use it right away. All I needed to do is to add [NotMapped] attribute to these properties. With that attribute I didn't need to make this complex workaround. I think I already found it out before and now I had to remember it again (especially as it was used in the original Items class and for some unknown reason I didn't use it in that new ItemsList class and therefore invented that whole problem for myself to solve and spent a few hours solving it).

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Marked as answer by Naomi N Thursday, February 5, 2015 10:36 PM
    • Edited by Naomi N Thursday, February 5, 2015 10:36 PM
    Thursday, February 5, 2015 10:36 PM

All replies

  • I found a solution (which I don't like much) in this answer

    https://stackoverflow.com/questions/5325797/the-entity-cannot-be-constructed-in-a-linq-to-entities-query#_=_

    So, my new code which works now is:

     var itemsList = _dbSetList.Where(x => x.CategoryId.Equals(categoryId)).Select(iList=> new {Item = iList.Item,
                DepartmeId = iList.DepartmeId,
                Department = iList.Department,
                ItemId = iList.ItemId,
                Category = iList.Category,
                CategoryId = iList.CategoryId,
                Descrip = iList.Descrip,
                Hidden = iList.Hidden,
                DepartmentNameHash = "",
                CategoryNameHash = "",
                ItemNameHash = ""
                }).ToList().Select(x => new ItemsList
                {
                    Item = x.Item,
                    DepartmeId = x.DepartmeId,
                    Department = x.Department,
                    ItemId = x.ItemId,
                    Category = x.Category,
                    CategoryId = x.CategoryId,
                    Descrip = x.Descrip,
                    Hidden = x.Hidden,
                    DepartmentNameHash = x.DepartmentNameHash,
                    CategoryNameHash = x.CategoryNameHash,
                    ItemNameHash = x.ItemNameHash
                });
    
                if (excludeHidden)
                {
                    itemsList = itemsList.Where(i => i.Hidden == false);
                }
    
                // Parameter sortType is set when we are getting items for 
                // the DCI tree view. Otherwise, it will just be NotSpecified.
    
                itemsList = SetItemSortOrder(itemsList.AsQueryable(), sortType);           
    
                foreach (var item in itemsList)
                {
                    if (item != null)
                    {
                        item.DepartmentNameHash = item.Department.ToBase64UrlEncoded();
                        item.CategoryNameHash = item.Category.ToBase64UrlEncoded();
                        item.ItemNameHash = item.Item.ToBase64UrlEncoded();
                    }
                }
    
                return itemsList;
            }
    So, I am now using double select. I haven't yet checked what query it generates.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, February 5, 2015 10:04 PM
  • The solution was much easier - strange that I didn't use it right away. All I needed to do is to add [NotMapped] attribute to these properties. With that attribute I didn't need to make this complex workaround. I think I already found it out before and now I had to remember it again (especially as it was used in the original Items class and for some unknown reason I didn't use it in that new ItemsList class and therefore invented that whole problem for myself to solve and spent a few hours solving it).

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Marked as answer by Naomi N Thursday, February 5, 2015 10:36 PM
    • Edited by Naomi N Thursday, February 5, 2015 10:36 PM
    Thursday, February 5, 2015 10:36 PM
  • Hello Naomi,

    It is glad to hear that you got it working and thank you for sharing your solutions & experience here. It will be very beneficial for other community members who have similar questions.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, February 6, 2015 1:50 AM
    Moderator