New Posts New Posts RSS Feed: EntitySql & Projection
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

EntitySql & Projection
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
sven4tmn View Drop Down
Newbie
Newbie
Avatar

Joined: 22-Feb-2011
Posts: 5 		Post Options Post Options   Quote sven4tmn Quote  Post ReplyReply Direct Link To This Post Topic: EntitySql & Projection
    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
Back to Top
DenisK View Drop Down
IdeaBlade
IdeaBlade


Joined: 25-Aug-2010
Posts: 715 		Post Options Post Options   Quote DenisK Quote  Post ReplyReply Direct Link To This Post Posted: 06-Jun-2011 at 10:51am
Hi sven4tmn;
Back to Top
sven4tmn View Drop Down
Newbie
Newbie
Avatar

Joined: 22-Feb-2011
Posts: 5 		Post Options Post Options   Quote sven4tmn Quote  Post ReplyReply Direct Link To This Post 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? 
Back to Top
DenisK View Drop Down
IdeaBlade
IdeaBlade


Joined: 25-Aug-2010
Posts: 715 		Post Options Post Options   Quote DenisK Quote  Post ReplyReply Direct Link To This Post 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 or Stored Procs, 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.


Edited by DenisK - 07-Jun-2011 at 12:16pm
Back to Top
sven4tmn View Drop Down
Newbie
Newbie
Avatar

Joined: 22-Feb-2011
Posts: 5 		Post Options Post Options   Quote sven4tmn Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
DenisK View Drop Down
IdeaBlade
IdeaBlade


Joined: 25-Aug-2010
Posts: 715 		Post Options Post Options   Quote DenisK Quote  Post ReplyReply Direct Link To This Post 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

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.
Back to Top
sven4tmn View Drop Down
Newbie
Newbie
Avatar

Joined: 22-Feb-2011
Posts: 5 		Post Options Post Options   Quote sven4tmn Quote  Post ReplyReply Direct Link To This Post 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?
Back to Top
DenisK View Drop Down
IdeaBlade
IdeaBlade


Joined: 25-Aug-2010
Posts: 715 		Post Options Post Options   Quote DenisK Quote  Post ReplyReply Direct Link To This Post 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


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.


Edited by DenisK - 05-Jul-2011 at 12:55pm
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down