C# Silverlight – Defining a join between database tables

cdatabasejoin;silverlight

Currently I have a c# silverlight business application. The application is hosted in Asp.net using the ADO.Net entity framework and a domain service class to read/write to/from my sql server database.

The structure of the database is as follow. I have three tables. Job, Audit & Image. One Job can have many Audits, and One Image can have many Audits – a fairly simple structure. In my Silverlight Client I have a datagrid which is bound to the Jobs table, I also have a listbox which I populate with the list of audit items directly related to the selected job in the datagrid. In order to do this I use the datagrids selection changed event to ensure that the audit listbox is populated with that particular set of audits for each job every time the job selection changes in the datagrid. The code for doing this is below:

private void dataGrid1_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {  

                var auditJob = dataGrid1.SelectedItem as Job;
                auditsList.ItemsSource = auditJob.Audit;
}

As you can see it's not rocket science, but of course this method works because of the one to many relationship between job and audit. (one job, many audits). I want to do something sort of similar but with the Image table (different relationship structure). This time I want to populate a listbox with the contents of my Image table, so that when a user selects a specific job in the datagrid, it shows the audits for that job and the list of images. However, since there is no direct link between job and image I am unsure of how to do this but would like to employ a similar method to the one I used above. Since one job has many audits and one image has many audits, the audit table thus contains its own AuditID primary key, as well as the JobID foreign key and the ImageID foreign key. My question is how do I go about setting that link up between Job and Image in order to produce a list of Images for each individual job?

Help would be greatly appreciated

Best Answer

I think this is a good description of your issue: http://microapplications.com/blog/archive/2009/04/18/329.aspx

Essentially, you can create a new class that contains job, image and audit properties and return it from your DomainService. On the server side you cobble together some LINQ code that'll select the related items from all 3 tables.

That's just one solution though, you could probably also write some code in your selectionchanged handler to just load images that are associated with the job in question.

This is the style that Microsoft tends to use in their examples.

It appears that the guidance in this area may have changed, as StyleCop now enforces the use of the C# specific aliases.

Sql – the difference between “INNER JOIN” and “OUTER JOIN”

Assuming you're joining on columns with no duplicates, which is a very common case:

An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.
An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.

Examples

Suppose you have two tables, with a single column each, and data as follows:

Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.

Inner join

An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.

select * from a INNER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

Left outer join

A left outer join will give all rows in A, plus any common rows in B.

select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b(+);

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

Right outer join

A right outer join will give all rows in B, plus any common rows in A.

select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a(+) = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

Full outer join

A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

Best Answer

Related Solutions

C# – the difference between String and string in C#

This is the style that Microsoft tends to use in their examples.

Sql – the difference between “INNER JOIN” and “OUTER JOIN”

Related Topic