Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>In order to answer this properly, I need to know if Application and Person are directly related (i.e. does Person have many Applications)? From reading your post, I'm assuming that they are because Application seems to have a foreign key to person. </p> <p>If so, then you could create a custom PersonModel which will be populated by the fields you need from the different entities like this:</p> <pre><code>class PersonModel { string Name { get; set; } string Surname { get; set; } List&lt;int&gt; Scores { get; set; } List&lt;int&gt; Positions { get; set; } } </code></pre> <p>Then to populate it, you'd do the following:</p> <pre><code>// Select the correct person based on Name and Surname inputs var person = dataContext.Persons.Where(p =&gt; p.Name.Contains("firstname") || p.Name.Contains("surname")).FirstOrDefault(); // Get the first person we find (note, there may be many - do you need to account for this?) if (person != null) { var scores = new List&lt;int&gt;(); var positions = new List&lt;int&gt;(); scores.AddRange(person.Applications.Select(i =&gt; i.Score); positions.AddRange(person.Applications.Select(i =&gt; i.Position); var personModel = new PersonModel { Name = person.Name, Surname = person.Surname, Scores = scores, Positions = positions }; } </code></pre> <p>Because of your relationship between Person and Application, where a person can have many applications, I've had to account for the possibility of there being many scores and positions (hence the List). </p> <p>Also note that I've used lambda expressions instead of plain linqToSql for simple selecting so that you can visualise easily what's going on. </p>
    singulars
    1. This table or related slice is empty.
    1. POHow to join multiple tables using LINQ-to-SQL?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USOdhran
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COI don't think projecting his results onto a new object is his problem. I think his question makes clear (especially considering his two code samples) that he wants to *join* these two tables together - using linq.
      singulars
      1. PO
      1. USAdam Rackis
    2. COAlright ... fair point. But in your code answer, I don't see anywhere how you account for the one to many relationship. You should be dealing with dataContext.Persons (not a single Person). Also, you haven't selected his many scores and positions in your anonymous return value. I was just trying to interpret the poor chaps question because he's obviously having serious trouble.
      singulars
      1. PO
      1. USOdhran
    3. COYou're right. When he queries for his people, and then joins to applications, he might end up with multiple rows for the same person since there are "many" corresponding applications. He's going to have to figure out how he wants to deal with that, or maybe he already has. The code I have him will get him the join he's looking for, hopefully he's aware of the issue you've pointed out. I also edited to add scores/positions. Again, hopefully he's aware that there could be many applications per person, and so multiple rows for each person.
      singulars
      1. PO
      1. USAdam Rackis
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload