Solved

LINQ to SQL - tables with foreign keys

Posted on 2013-05-16
3
709 Views
Last Modified: 2013-06-04
Hello Experts,
I am having issue with writing LINQ to SQL query:

I use Entity Framework to generate Entities and try to write LINQ to extract data...

Tables:
OrderHeader  (OrderHeaderId - primary key)
OrderPayment (OrderHeaderId - foreign Key)

When i generate entities...

In OrderPayment  - i am not seeing OrderHeaderId (get set)...since they are keyed that may be the reason i am not seeing this field.


how to join this both tables in LINQ...


Here is my LINQ:  ( it is not liking bolded line..)

 lstBatchSettleRequestInfo = (from oh in _edgePier1DataConnection.OrderHeaders
                                     join op in _edgePier1DataConnection.OrderPayments on  oh.OrderHeaderId = op.OrderHeaderId                                     join reg in _edgePier1DataConnection.DynamicFields on oh.DynamicRecordId equals reg.DynamicRecordId
                                     join trans in _edgePier1DataConnection.DynamicFields on oh.DynamicRecordId equals trans.DynamicRecordId
                                     where  op.PaymentStatusId == 1
                                            && oh.OrderTypeId != 3
                                            && (op.TransactionTypeId == 52 || op.TransactionTypeId == 53)
                                            && oh.OrderHeaderId >= 9999999
                                            && reg.DynamicFieldLayoutId == 4 && reg.DynamicFieldValue != null
                                            && trans.DynamicFieldLayoutId == 5 && trans.DynamicFieldValue != null
                                     orderby oh.OrderHeaderId, op.CreateDate ascending
                                     select new BatchSettleRequestInfo
                                            {MerchantReferenceCode = oh.OrderHeaderId,
                                            PaymentCreateDate = op.CreateDate,
                                            PurchaseTotals_GrandTotalAmount = op.Amount,
                                            StoreID = oh.ReferenceField3,
                                            ccCaptureService_run = op.TransactionTypeId == 52 ? "true" : "",
                                            ccCaptureService_authRequestID = op.TransactionTypeId == 52 ? op.AuthTrnNo : "",
                                            ccCreditService_run = op.TransactionTypeId == 53 ? "true" : "",
                                            ccCreditService_captureRequestID = op.TransactionTypeId == 53 ? op.AuthTrnNo : ""
                                            }).Distinct().ToList();




any idea?

Thanks
0
Comment
Question by:mani_sai
  • 2
3 Comments
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 500 total points
ID: 39172034
try this change

join op in _edgePier1DataConnection.OrderPayments on  oh.OrderHeaderId equals op.OrderHeaderId   ...

AW
0
 
LVL 8

Author Closing Comment

by:mani_sai
ID: 39219622
Thanks
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 39219759
Glad to be of assistance
AW
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now