Solved

LINQ to SQL - tables with foreign keys

Posted on 2013-05-16
3
714 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

777 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