Query inside LinQ query

Question

0.00/5 (No votes)

See more:

Hi,

A few tables in my database which look like below,

A job (job-table) has multiple parts (job-parts-table). The job-parts has two columns (pickup address) and (destination address). Even though each job-part will have only one (pickup address)and one (destination address), I want to keep the address details in separate table (address table). Now when I query for address details for a specific job I want the address details rather than address ID.

relations
----------
one (job) to many (parts)
one (parts) to one (address) / Actually one job-part will have two addresses

query
------
from j in job
from jp in j.job-parts
where j.customerID = 1
select new with{
jp.pickup-address-ID,
jp.destination-address-ID})

How can I get address details rather than ID.
Thank you in advance.

Posted 3-Jun-13 14:32pm

adat7378

Add a Solution

Comments

PrissySC 3-Jun-13 21:06pm

Let's see if I remember this correctly ... Anyone can correct me ...

When you use select new with, returns an anonymouse object/type as much as select new.

So ....
You should do Select New Customer With {.pickup-address-ID, .destination-address-id}

Of course, the Customer will be whatever entity/class you have.

Now as for the details, if you don't need an anonymouse type, you could just use a straight Select without the new. Depends if you want the whole record not.

adat7378 3-Jun-13 21:34pm

Thanks. I got the point. Unfortunatly I am only trying to learn LinQ. Could you please show me the query?

My tables are like,

(Job)
JobID
CustomerID
JobPartID

Job -> JobPart (One to Many)
(JobPart)
JobPartID
JobID [Foreign Key – ref JobID(JobID)]
Pickup-AddressID [Foreign Key – ref Address(AddressID)]
Destination-AddressID [Foreign Key – ref Address(AddressID)]

Address is a separate table
(Address)
AddressID
HouseNo
Street
City
Etc..

I get both address IDs with the below. I want the city and the street from the address table for each addredd ID.

Thanks again.

var results =
from j in job
from jp in j.JobParts
where j.CustomerID == 1
select new { jp.PickupAddressID, jp.DestinationAddressID };

PrissySC 4-Jun-13 11:23am

To pull the full record for the entire j table ...

From j in job Where j.customerID = 1 Select j

Get only specific fields and join the two tables 1-Many ...

From j in job, jp in job-parts Where j.customerID = 1 Select j.jobID, jp.streetaddress, jp.zipcode

My favorite link of all ...
101 Visual Basic LINQ Samples is the main page, but this is specifically LINQ To SQL Samples.

http://msdn.microsoft.com/en-us/vstudio/bb688085.aspx

So ...
var results =
from j in job
from jp in jobParts
from a in address
where j.CustomerID and j.JobPartID=jp.JobPartID and jp.Destination-AddressID = a.AddressID
Select jp.PickupAdressID, jp.Destination-AddressID

Unless you truly need an anon type pull full record. If you used any of the wizards, these classes/types have already been created in your main dataclass .

Just a note too ... before you use check if it exists. You need to handle if you don't return anything.

C# examples ... http://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b It's a download.

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)