troubleshooting Question

Entity Framework Raw Sql , How to Join two tables and assign it to model

Avatar of nicedone
nicedone asked on
.NET ProgrammingMicrosoft SQL Server 2008SQL
3 Comments1 Solution1213 ViewsLast Modified:
SELECT Submission.SubmissionID, Submission.PostingID, Submission.ApiKey, Submission.CampaignCode, Submission.UserID, Submission.SessionID, Submission.Title, Submission.TitleOther, Submission.Firstname, 
                         Submission.Middlename, Submission.Surname, Submission.AdditionalSurname, Submission.AddressType, Submission.AddressLine1, Submission.AddressLine2, Submission.AddressLine3, Submission.County, 
                         Submission.City, Submission.Province, Submission.PostCode, Submission.State, Submission.Country, Submission.PrivateEmail, Submission.BusinessEmail, Submission.PrivateTelephone, 
                         Submission.BusinessTelephone, Submission.MobileTelephone, Submission.Fax, Submission.SubmissionTypeID, Submission.DateCreated, Submission.Culture, SubmissionDetail.SubmissionDetailID, 
                         SubmissionDetail.SubmissionID AS Expr1, 
						 (Select TOP(1) fieldvalue from submissiondetail where submissiondetail.submissionid = submission.submissionid and fieldname = 'MailOptIn') AS MailOptInValue,
						 (Select TOP(1) fieldvalue from submissiondetail where submissiondetail.submissionid = submission.submissionid and fieldname = 'PhoneOptIn') AS PhoneOptInValue,
						 (Select TOP(1) fieldvalue from submissiondetail where submissiondetail.submissionid = submission.submissionid and fieldname = 'SMSOptIn') AS SMSOptInValue,
						 (Select TOP(1) fieldvalue from submissiondetail where submissiondetail.submissionid = submission.submissionid and fieldname = 'EMailOptIn') AS EMailOptInValue,
						 (Select TOP(1) fieldvalue from submissiondetail where submissiondetail.submissionid = submission.submissionid and fieldname = 'FormId') AS FormIdValue,
						 (Select TOP(1) fieldvalue from submissiondetail where submissiondetail.submissionid = submission.submissionid and fieldname = 'CustomerType')AS CustomerTypeValue,
						 (Select TOP(1) fieldvalue from submissiondetail where submissiondetail.submissionid = submission.submissionid and fieldname = 'DealerId') AS DealerIdValue,
						 (Select TOP(1) fieldvalue from submissiondetail where submissiondetail.submissionid = submission.submissionid and fieldname = 'ContactConcern') AS ContactConcernValue,
						 LTRIM(RTRIM((Select TOP(1) fieldvalue from submissiondetail where submissiondetail.submissionid = submission.submissionid and fieldname = 'Comments'))) AS CommentsValue
FROM          Submission INNER JOIN SubmissionDetail ON Submission.SubmissionID = SubmissionDetail.SubmissionID
WHERE        (Submission.DateCreated BETWEEN '2014-07-10' AND '2014-07-28 13:30') AND (Submission.Culture = 'en-EN') AND (Submission.SubmissionTypeID = '2')

I have two tables in my sql database and have many records i want to access my database table through Entity Framework and query the table with above command , the problem I am having is in entity framework you choose which table to execute the query on but in my case I will have two tables to join so how will i do that?
In my db below I choose the submissions table to work on but in  my case I really need both the submissions and submissiondetails tables, how should i accomplish that. Another thing is my query returns about 5000 rows assigning it to a var obj ,would this work ?
I dont have to use raw sql, i found it difficult to convert it to linq statement. Can you help me with the modification of my model and/or sql statement so that it gets executed.

 
 using (var db = new SubmissionModel())
            {
                var query = db.Submissions.SqlQuery().ToList();
             }
ASKER CERTIFIED SOLUTION
nicedone

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros