?
Solved

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

Posted on 2014-08-12
3
Medium Priority
?
463 Views
Last Modified: 2015-01-14
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

0
Comment
Question by:nicedone
  • 2
3 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 40256780
Hi,

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

Regards
  David
0
 

Accepted Solution

by:
nicedone earned 0 total points
ID: 40257984
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
 

Author Closing Comment

by:nicedone
ID: 40548558
this problem no longer exists, performance was not an issue on the query
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

840 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