Print Page | Close Window

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.



Print Page | Close Window