none
Problem in constructing custom column in L2E RRS feed

  • Question

  • Hello all,

    I am new to ado.net entity framework. I generally write SQL queries and now i start learning entity framework. I am facing little problem in constructing custom column in entity framework which i can write easily in simple SQL query. so can anyone please correct or replace my SQL query to L2E query. Please see code in bold lines.

    SELECT TOP(20) Id, ProductId, ProductName,  'detail.aspx?pid=' + cast(Id as nvarchar(20)) + '&cid=' + CategoryId + '&Cn=' + CategoryName AS detail FROM Products WHERE NewArrivals = 1

    Please see the above bold line where i am unable to convert into entity framework L2E and try to make similar in the same way below which is not correct. So can anyone help to write the above SQL query in form of L2E ???

     Dim db As New dbRohiModel.dbRohiEntities
            Dim MyProducts = From cs In db.Products Where cs.NewArrivals = 1 _
                                     Select cs.Id, cs.ProductId, cs.ProductName, cs.SmallImagePath, 'detail.aspx?pid=' + cast(Id as nvarchar(20)) + '&cid=' + CategoryId + '&Cn=' + CategoryName AS detail

    Thanks in advance.


    • Edited by Rauf Abid Friday, March 8, 2013 9:24 AM
    Friday, March 8, 2013 7:05 AM

Answers

  • Hi,

    The way that LINQ to Entities works is that it takes a LINQ expression written in your language, VB in this case, and eventually converts the expression tree from that LINQ statement into SQL for whichever type of database you are working with.

    Unfortunately, it looks like VB is generating an expression tree that EF doesn't know how to handle. Specifically, it doesn't know how to convert the overload of Concatenate that takes an array of strings. There are 3 ways I can think of at the moment that you could work around this.

    The first is to split up the concatenate so that each call to the function has fewer parameters, so that it will use an overload that EF can handle.

    It would look like this:

    Select cs.Id, cs.ProductId, cs.ProductName, cs.SmallImagePath, detail = String.Concat("detail.aspx?pid=" + (cs.Id.ToString), "&cid=" + cs.CategoryId, "&Cn=" + cs.CategoryName)

    With the above select you are splitting up the call to the concat function into multiple calls with fewer parameters.

    The next way to work around this would be to do the string concatenation on the server:

    Dim productsQuery = From cs In db.Products Where cs.NewArrivals = 1 Select cs

    Dim myProducts = From cs in productsQuery.AsEnumerable() Select cs.Id, cs.ProductId, cs.ProductName, cs.SmallImagePath, detail = "detail.aspx?pid=" + (cs.Id.ToString) + "&cid=" + cs.CategoryId + "&Cn=" + cs.CategoryName


    The above query should work, but it will change the way the query works. In the first work-around you would only be selecting the columns from the database that are in the select. But in this case it would select everything from the table that is included in the Product entity, probably every column in the table, so you will most likly be bringing back more data from the server than you need. This may or may not be a problem for you in this case.

    The last way to help resolve this is to use the Product entity that you are selecting. The Product entity is a class in your application, you could add a Description property to it that concatenates the strings for you.
    How you do this would depend on which version of EF you are using and whether you are using Code First or not. It would have similar changes to the second suggestion to your query, but it would remove the second projection part of the query.

    I couldn't reproduce this problem in C#, so I am assuming there is difference in the way that C# and VB generate expression trees for concatenation. I have filed a bug with the EF team on it here: http://entityframework.codeplex.com/workitem/944


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.

    Saturday, March 9, 2013 1:06 AM
    Moderator

All replies

  • Hi,

    The trick is that a LINQ expression is not written in SQL but in the host programming language which is then translated to SQL. So it would be something like :

    ...detail="detail.aspx?pid" +Id.ToString etc...

    When using VB using & might work...

    If you need further help please always state the exact error message you get (for now the first problem I see is the use of SQL specific constructs inside your LINQ statement). At some point you'll likely have an error message if you are trying to call something which as no server side SQL counterpart...


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

    Friday, March 8, 2013 11:51 AM
  • Thanks for the reply,

    Error gone but detail alias not working it is giving error like following

    LINQ to Entities does not recognize the method 'System.String Concat(System.String[])' method, and this method cannot be translated into a store expression.

    I write L2E in the following way which is giving above error.

    Select cs.Id, cs.ProductId, cs.ProductName, cs.SmallImagePath, detail = "detail.aspx?pid=" + (cs.Id.ToString) + "&cid=" + cs.CategoryId + "&Cn=" + cs.CategoryName


    Please help, thanks.




    • Edited by Rauf Abid Friday, March 8, 2013 12:59 PM
    Friday, March 8, 2013 12:30 PM
  • So it seems to tell that it can't translate a client side string concatenation to a server side string concatenation ? What is the version of EF you are using (in case support would depends on which version you are using) so that one can test in the same condition ?

    What if you try & rather than + ?

    If this is really not supported, depending on which control will produce the link you should be able to use a list of fields and a format string such as "details.aspx?pid={0}&cid={1}&Cn={2}" to have the control produce the needed link using just the individual fields (or you could perhaps just pass the pid as you are likely able to fetch the categoryid and name for this product on the details page from the pid value ?)


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".


    Friday, March 8, 2013 3:35 PM
  • Hi,

    The way that LINQ to Entities works is that it takes a LINQ expression written in your language, VB in this case, and eventually converts the expression tree from that LINQ statement into SQL for whichever type of database you are working with.

    Unfortunately, it looks like VB is generating an expression tree that EF doesn't know how to handle. Specifically, it doesn't know how to convert the overload of Concatenate that takes an array of strings. There are 3 ways I can think of at the moment that you could work around this.

    The first is to split up the concatenate so that each call to the function has fewer parameters, so that it will use an overload that EF can handle.

    It would look like this:

    Select cs.Id, cs.ProductId, cs.ProductName, cs.SmallImagePath, detail = String.Concat("detail.aspx?pid=" + (cs.Id.ToString), "&cid=" + cs.CategoryId, "&Cn=" + cs.CategoryName)

    With the above select you are splitting up the call to the concat function into multiple calls with fewer parameters.

    The next way to work around this would be to do the string concatenation on the server:

    Dim productsQuery = From cs In db.Products Where cs.NewArrivals = 1 Select cs

    Dim myProducts = From cs in productsQuery.AsEnumerable() Select cs.Id, cs.ProductId, cs.ProductName, cs.SmallImagePath, detail = "detail.aspx?pid=" + (cs.Id.ToString) + "&cid=" + cs.CategoryId + "&Cn=" + cs.CategoryName


    The above query should work, but it will change the way the query works. In the first work-around you would only be selecting the columns from the database that are in the select. But in this case it would select everything from the table that is included in the Product entity, probably every column in the table, so you will most likly be bringing back more data from the server than you need. This may or may not be a problem for you in this case.

    The last way to help resolve this is to use the Product entity that you are selecting. The Product entity is a class in your application, you could add a Description property to it that concatenates the strings for you.
    How you do this would depend on which version of EF you are using and whether you are using Code First or not. It would have similar changes to the second suggestion to your query, but it would remove the second projection part of the query.

    I couldn't reproduce this problem in C#, so I am assuming there is difference in the way that C# and VB generate expression trees for concatenation. I have filed a bug with the EF team on it here: http://entityframework.codeplex.com/workitem/944


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.

    Saturday, March 9, 2013 1:06 AM
    Moderator
  • Thanks for the reply, and sorry for the late reply because of two holidays.

    First of all iam using entity framework 5 and database first approach. Iam writing some queries which are working and which are not working from the help of above answers

    1- Not working (detail = "details.aspx?pid={0}&cid={3}&Cn={4}")

    2- Not Working (detail = String.Concat("detail.aspx?pid=" + (cs.Id.ToString), "&cid=" + cs.CategoryId, "&Cn=" + cs.CategoryName))

    3- Working (Dim productsQuery = From cs In db.Products Where cs.NewArrivals = 1 Select cs
    Dim myProducts = From cs in productsQuery.AsEnumerable() Select cs.Id, cs.ProductId, cs.ProductName, cs.SmallImagePath, detail = "detail.aspx?pid=" + (cs.Id.ToString) + "&cid=" + cs.CategoryId + "&Cn=" + cs.CategoryName)

    Actually i am interested in query number 2. Please help to improve or correct which is giving the following error.

    LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression.



    • Edited by Rauf Abid Monday, March 11, 2013 6:12 AM
    Monday, March 11, 2013 5:46 AM
  • The exception message for ToString is their for the same reason as the other error was being thrown, EF doesn't convert it to a server side function. However, it was working in the quick sample I put together on my machine. Which versions of EF, SQL Server, and Visual Studio are you using?

    One quick thing to try though is to just remove the ToString and replace the + with an &.


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.

    Monday, March 11, 2013 6:33 PM
    Moderator
  • Thanks Glenn,

    I am using sql server 2005 with Entity framework 5. I tried The workaround you described but no luck. I tried the following code.

     Select cs.SmallImagePath, detail = String.Concat("detail.aspx?pid=" + (cs.Id) + "+cid=" + cs.CategoryId, "+Cn=" + cs.CategoryName)

    The above code is giving the following error.

    Conversion from string "detail.aspx?pid=" to type 'Double' is not valid.

    Thanks.

    Wednesday, March 13, 2013 4:29 AM
  • And if you replace the + with an & in the line you have above?

    I think the reason for the error about a double is because the + is being interpreted as addition, since one of the operands is not a string. If you use & then that shouldn't happen, see http://msdn.microsoft.com/en-us/library/te2585xw(v=vs.110).aspx


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.

    Wednesday, March 13, 2013 4:40 PM
    Moderator
  • Thanks Glenn,

    I actually figure out the problem and solve it by writing the following query

    Dim MyProducts = From cs In db.Products.AsEnumerable Where cs.NewArrivals = True _
            Select cs.Id, cs.ProductId, cs.ProductName, cs.SmallImagePath, detail = "detail.aspx?pid=" & cs.Id.ToString & "&cid=" & cs.CategoryId & "&Cn=" & cs.CategoryName

    Can you please explain that what is AsEnumerable ??? and why to use it ??? because after writing this it recognize "Tostring" function, Please explain in your own simple words,  thanks.

    My second question is:-

    The query you explain below by adding "String.Concat" should work but it is not working after replacing + and & signs with each other also. What can be the reason?

     detail = String.Concat("detail.aspx?pid=" + (cs.Id.ToString), "&cid=





    • Edited by Rauf Abid Thursday, March 14, 2013 5:37 AM
    Thursday, March 14, 2013 5:13 AM
  • Hi,

    It should take all products client side and do the rest so at lists apply to the where criteria and then only do the rest client side.

    Curiously I never ran into this issue as my approach is generally :

    1) to consume the fields for example a control usually can use a format string to assemble several fields you already have (this is what I do for links)

    2) or I expose a client side propertyr read only that does the calculation from the properties I got...

    I'll give this a try...


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

    Thursday, March 14, 2013 11:01 AM