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

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')

Open in new window


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();
             }

Open in new window

nicedoneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior DBACommented:
Hi,

What does the query plan look like with all those subqueries? It can't be good for performance.

Regards
  David
0
nicedoneAuthor Commented:
There is a pair-value column on the table so with subqueries i am making one column and value of that column comes with the second(value) column. I need those to be able to query that is fine it takes long but already returns me the result and i am fine with the performance. Priority is the getting query in entity framework and working with multiple tables but if you can do both(address the performance issue and work with 2 tables in EF) than that is more than WELCOME :)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nicedoneAuthor Commented:
this problem no longer exists, performance was not an issue on the query
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.