Sarel (3) [Avatar] Offline
#1
Hi.
I am looking for the LINQ to do a simle sub-query and I am ready to cry!!! The sql for this is quite simple:
The main part of the query is : select * from tblProperties

I have an image table attached to this(one to many) where tblProperties.imageID = tblImages.imageID
and all i want is all propery rows with one random image per property.

The SQL that I used looks something like:
select propertyName, (select top 1 imageLocation from tblImages order by newID()) as randomImage
from tblProperties

what would be the LINQ for this??

Thank you
Sarel
jwooley (123) [Avatar] Offline
#2
Re: LINQ sub query
I'm not sure that LINQ to SQL supports the CLR based Random or TSQL based newID natively. I can see a couple options:

1) Use a pass-through query to get the results
2) Use a view to return the ID of the record from tblImages from your subselect and map to that view. Then use a LINQ query to join the tblProperties through the Randomize view and then to the tblImages
3) Use a scalar TSQL Function to get a random picture and include that in your LINQ query.
4) Use a stored procedure to return the results.

We discuss consuming pass-through queries, functions and stored procedures in chapter 8. Read through that section and see if it helps.
fabrice.marguerie (224) [Avatar] Offline
#3
Re: LINQ sub query
To add to Jim's reply, here is how to create a function that returns a random GUID:

CREATE VIEW RandomView
AS
SELECT NEWID() As ID

GO

CREATE FUNCTION GetNewId
(
)
RETURNS uniqueidentifier
AS
BEGIN
RETURN (SELECT ID FROM RandomView)
END

You can then map the GetNewId user defined function using LINQ to SQL's Function attribute. As indicated by Jim, see chapter 8 for the details.
jwooley (123) [Avatar] Offline
#4
Re: LINQ sub query
Actually, Fabrice, in your example you are just pulling a random GUID regardless of whether it appears in the image table or not. In this example, you would want the function to return the imagelocation rather than the ID. Thus the function would be something along the lines of:

CREATE FUNCTION GetRandomImage
()
RETURNS nvarchar(255)
AS
BEGIN
RETURN (select top 1 imageLocation from tblImages order by newID())
END
fabrice.marguerie (224) [Avatar] Offline
#5
Re: LINQ sub query
You're right, but that was on purpose. Mapping a generic function like the one I suggest allows us to use it for more queries than just the ones where you need a random image. It could be used each time randomization is required in a LINQ to SQL query.
Sarel (3) [Avatar] Offline
#6
Re: LINQ sub query
Thanks for that reply!! Ok, let's say that I create the function to return a random image based on the propertyID and I call it GetRandomImage(@propertyID) what would the LINQ be to get the property and the random image??

Is this book only available in paper format?? Can I download a pdf somewhere?? I am in South-Africa and by the time the book gets here I do not need it any more.

Thanks once again for the reply, do you also know of a good forum to get LINQ answers??

Sarel
Sarel (3) [Avatar] Offline
#7
Re: LINQ sub query
Thanks, chapter 8....... how is the fastest way for me to get hold of this??

Thanks you
Sarel
fabrice.marguerie (224) [Avatar] Offline
#8
Re: LINQ sub query
In fact, the book won't be available in paper before the end of this month. But it has been available in PDF for several months already. See http://manning.com/marguerie