DevForce 2010: EntitySql & Projection

EntitySql & Projection

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2010
Forum Discription: For .NET 4.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=2743
Printed Date: 09-Jun-2026 at 11:42pm

Topic: EntitySql & Projection

Posted By: sven4tmn
Subject: EntitySql & Projection
Date Posted: 06-Jun-2011 at 3:19am

Hi,

I'm using EntitySql in a Silverlight client to retrieve my data. Due to complex queries and functions inside SqlServer, I wasn't able to use Linq. As long as I am retrieving whole Entites (e.g. Select Value xy from Entity as xy ...) I have no problems. But now I need to return some value calculated sqlserver-side along the entity, so I need a projection. Sorry, I can figure out how to do that. I already tried to extend my Entity with an additional property but the I wasn't able to fill it with the function's return value. Maybe you can help me a little bit.

Thanks in advance,

Sven

Replies:

Posted By: DenisK
Date Posted: 06-Jun-2011 at 10:51am

Hi sven4tmn;

See if this page can help you. http://drc.ideablade.com/xwiki/bin/view/Documentation/query-anonymous-projections - http://drc.ideablade.com/xwiki/bin/view/Documentation/query-anonymous-projections

Posted By: sven4tmn
Date Posted: 06-Jun-2011 at 10:50pm

Hi Denis,

Thanks for your response. Do you have some documentation using projection in EntitySql instead of Linq?

Posted By: DenisK
Date Posted: 07-Jun-2011 at 11:49am

Hi sven4tmn;

For some reason, I didn't see that you've said you couldn't use LINQ. My apologies.

Unfortunately, for the time being DevForce doesn't support projection with E-SQL. My immediate suggestions are to consider using RSMC, http://drc.ideablade.com/xwiki/bin/view/Documentation/rsmc-query - http://drc.ideablade.com/xwiki/bin/view/Documentation/rsmc-query or Stored Procs, http://drc.ideablade.com/xwiki/bin/view/Documentation/stored-procedure-queries - http://drc.ideablade.com/xwiki/bin/view/Documentation/stored-procedure-queries .

I might be able to give more specific suggestions if you can let me know your specific use case.

Posted By: sven4tmn
Date Posted: 30-Jun-2011 at 4:33am

Hi Denis,

I've tried to find a good solution using SPs but it didn't work out for me. So maybe you can give me another advice. My problem is that I need to call a SQL (scalar) function which returns a rating (int number) along with the entity. Based on the rating I have to sort the entities in my Silverlight client. As the query may get very complex (where-clause with many sub-selects) and because I have to call SQL functions in it I switched to EntitySql.

Any ideas I can handle this?

Thanks,

Sven

Posted By: DenisK
Date Posted: 30-Jun-2011 at 11:52pm

Hi Sven;

I'm still trying to understand why you need to use EntitySql. You said that the query may get very complex with where-clause and many sub-selects, as well as using SQL scalar functions, but I still don't see why this can't be done with LINQ. LINQ queries are pretty powerful as shown by these examples. http://drc.ideablade.com/xwiki/bin/view/Documentation/query-101-examples - http://drc.ideablade.com/xwiki/bin/view/Documentation/query-101-examples

You said that you already tried to extend your entity with the "rating" property but you weren't able to fill it with the function's return value. Is this a stored proc function that you're talking about? What was the issue that you're encountering with this?

Perhaps, if you can show me an example of the complex query, I'll be able to help better.

Posted By: sven4tmn
Date Posted: 01-Jul-2011 at 3:53am

Hi Denis,

well I tried to use LINQ but I wasn't able to execute the SQL scalar function (not a stored procedure). I didn't know how to create the function mapping on my Silverlight client. I found resources that uses EdmFunctionAttribute to get the functions work with LINQ, but it's not available in Silverlight... So I stuck with EntitySql:

My query looks like:

select value mye from MyEntity as mye where [the complex stuff across my database]

but I need to give a function's value along with the entity:

select value mye, dbo.MyFunction(mye.SomeField) as Rating from ...

I also tried (and failed) using:

select value MyEntity([..all fields..], dbo.MyFunction(mye.SomeField) as Rating) from ...

Well, if functions do not work in Silverlight + LINQ and projection does not work with EntitySql, do I have other options?

Posted By: DenisK
Date Posted: 05-Jul-2011 at 12:54pm

Hi Sven;

Thanks for the more detailed explanation. Based on this info, I have some options that I can think of.

1. Functions can't be imported and used in LINQ but Stored Procedures can. So one suggestion is to rewrite your SQL Scalar Functions to SQL Stored Procs.

2. If you have to stick with Functions, consider re-writing your query such that it doesn't use projections. In other words, separate the query into 2 queries.

eg:

select value mye from MyEntity as mye where [the complex stuff across my database]

once you have your mye, then you can do this next call.

select dbo.MyFunction(mye.SomeField) as Rating from ...

3. Consider using Views or DefiningQuery for this complex database query. http://blogs.microsoft.co.il/blogs/gilf/archive/2008/05/14/how-why-use-definingquery-element.aspx - http://blogs.microsoft.co.il/blogs/gilf/archive/2008/05/14/how-why-use-definingquery-element.aspx

I wish I could be more helpful. We have our Professional Services if you'd like to find out more about best practices in solving this kind of problems.