Solved

LINQ to SQL and dynamic left outer join

Posted on 2008-10-28
18
4,998 Views
Last Modified: 2013-12-17
Hi

I've created a LINQ to SQL DataContext class. The designer shows the correct joins on the tables.

How do I query the joined tables in the from clause? Do I need to still specify the join in the code?

Also can I do the join to the second table only if I need it? i.e. I only need to join the table to search values in one of the columns if there are some values provided.


Many thanks
Andrew

var query = from o in db.CommercialOrgs

    select o;
 

// query = new query with left outer join 

// based on CommercialOrgs and another table

Open in new window

0
Comment
Question by:j055
  • 9
  • 7
18 Comments
 

Author Comment

by:j055
ID: 22824301
I've just found out that the O/R designer in VS2008 only supports 1:1 mapping relationships. I guess this means I do need to provide all the join code for my one to many relationships?

I look forward to any suggestions regarding my initial question.

Thanks
Andrew

0
 
LVL 4

Expert Comment

by:novynov
ID: 22824553
I'm not sure what you mean by "only supports 1:1 mapping relationships." The O/R designer indeed supports 1:n relationships, assuming your FK relationships are setup.

given a DataContext with Person and Address objects/tables with a 1:n relationship between the two, I can query the n side like "context.Persons.Where(p => p.Id == personId).Addresses;"

Continuing with the example, I can also produce a flattened list of Addresses through the use of context.Persons.Where(p => p.IsActive == true).SelectMany().

That said, this will only give you inner joins. In order to do outer joins, you can do an explicit join and use DefaultIfEmpty().

I've answered some other questions about left outer joins. Here's a link to one of them, involving compound join criteria:

http://www.experts-exchange.com/Programming/Languages/.NET/LINQ/Q_23839071.html

Let me know if this helps. If not, I can give you a more specific left outer join example for your situation.

0
 

Author Comment

by:j055
ID: 22829831
I'm wondering if the OR designer only creates the joins if each table has a primary key.

CommercialOrgs has a 1:n relationship to OrgContacts. The code attached produces the correct inner join.

CommercialOrgs also has a 1:n relationship with Orgs_MarketSectors but this table doesn;'t have a primary key. It does show in the designer with the correct primary - foreign key join but I can't do a similar query with the Orgs_MarketSectors table because it's not strongly typed.

Do I have to create the join manually?

Thanks
Andrew

        using (OrgsDataContext db = new OrgsDataContext())

        {

            var query = from o in db.CommercialOrgs

                        //from ms in db.Orgs_MarketSectors

                        from c in o.OrgContacts

                        select new { c.OrgID, Org = o.OrgID };

            

            GridView1.DataSource = query;

            GridView1.DataBind();

        }

Open in new window

0
 
LVL 4

Expert Comment

by:novynov
ID: 22830742
I'm not sure what you mean by "it's not strongly typed."

You mention that in the designer you see the corect primary/foreign key join...yet you said that Orgs_MarketSector doesn't have a primary key. I must be missing something, but these two statements appear contradictory. Can you please clarify?

Please forgive the questions, I'm just trying to get a complete understanding of your exact problem...so I can help.
0
 

Author Comment

by:j055
ID: 22831057
I'm stuggling with a number of concepts which is why I'm not explaining myself very well I think.

I've attached a screen shot which I hope might help to explain things a bit better.

I don't understand why I can do this:

            var query = from o in db.CommercialOrgs
                        from c in o.OrgContacts
                        where c.ContactID == 1
                        select o;

but no this:

            var query = from o in db.CommercialOrgs
                        from m in o.Orgs_MarketSectors
                        where m.ID == 1
                        select o;



OrgsDataContext.GIF
0
 

Author Comment

by:j055
ID: 22831296
I'd like to be able to assembly my query piece by piece. This is what I'd like to do:


        using (OrgsDataContext db = new OrgsDataContext())

        {

            var query = from o in db.CommercialOrgs

                        select o;
 

            int[] sectors = new[] { 1, 3 };

            if (sectors.Length > -1)

            {

                // create an inner join on CommercialOrgs and Orgs_MarketSectors

                // then add a where clause to search for the sectors

                query = query.Where(ms => sectors.Contains(ms.ID));

            }
 

            Response.Write(query.ToString());

        }

Open in new window

0
 
LVL 4

Expert Comment

by:novynov
ID: 22831449
The picture helps a bit. So, assuming that the two associations map to a FK relationship in the db (i.e. you didn't add either association manually), both should work fine. Are you getting an error, or just wrong results?

I suspect the problem may be with the lack of PK on Orgs_MarketSector. I'm doing some experimenting right now to confirm. I'll get back to you ASAP.
0
 

Author Comment

by:j055
ID: 22831561
Yes I checked by adding a PK to the Orgs_MarketSector table and then dropping it on the designer. It then works as expected. I don't see why I need a PK however. The query is still the same with or without. I guess it's a querk with the designer.

Anyway it confused me and distracted me from the real issue about joins.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 4

Expert Comment

by:novynov
ID: 22831612
So, I was able to reproduce your issue....and it does appear that the problem is with the lacking PK.

Here is the warning I received in VS...

"The Type element 'Orgs_MarketSector' contains the Association element 'CommercialOrg_Orgs_MarketSector' but does not have a primary key.  No code will be generated for the association"

So...unless you can add the PK to that table, you'd be left doing a manual join.

Regarding your proposed query: how are you planning on using the int[] sectors? Are you wanting to filter via an OR or an AND with each sector? If you build up the query stepwise (i.e. q = q.Where(...); q = q.Where(...)), it will be an AND...Other methods are required to get this kind of "dynamic" building of an OR (i.e. one in which you don't know the composition and count of the ORed expressions in the where statement.

0
 

Author Comment

by:j055
ID: 22831631
Hi again

I don't know if this helps but this is the SQL I want to create dynamically.

Sorry again if I'm not explaining myself very well
-- SQL if values exist

SELECT DISTINCT t0.OrgID, t0.Organisation

FROM         CommercialOrgs AS t0 INNER JOIN

                      Orgs_MarketSectors AS t1 ON t0.OrgID = t1.OrgID

WHERE     (t1.ID IN (1, 3))
 

-- SQL if no values exist

SELECT DISTINCT OrgID, Organisation

FROM         CommercialOrgs AS t0

Open in new window

0
 

Author Comment

by:j055
ID: 22831720
Yes I do want to AND things together so if I had some other data to search in another table the SQL might look like this:
SELECT DISTINCT t0.OrgID, t0.Organisation

FROM         CommercialOrgs AS t0 INNER JOIN

                      Orgs_MarketSectors AS t1 ON t0.OrgID = t1.OrgID INNER JOIN

                      Orgs_Activities AS t2 ON t0.OrgID = t2.OrgID

WHERE     (t1.ID IN (1, 3)) AND (t2.ID IN (5, 6, 7))

Open in new window

0
 
LVL 4

Expert Comment

by:novynov
ID: 22831818
Your SQL helps greatly...So...getting an IN to be generated by the SQL provider isn't exactly straightforward, but is entirely possible.

The key is "flipping" your Where statement around to filter based on the OrgId being in the array (or whatever data structure you use). Here's a link to an article that deals with the approach:

http://blog.wekeroad.com/2008/02/27/creating-in-queries-with-linq-to-sql/

That said, I did a quick experiment with my scaled down version of your schema. See the linq statement and associated SQL below.

Having that side of it, you could have 2 linq queries - this one, and the one for the case you have no values...and just "call" the right query.

Does this give you enough to solve your problem? If not, I'll do more.

Your explanations are fine. Please forgive me if my answers aren't quick and in-depth. I'm balancing this with some work I've got to get done...so it isn't my primary focus at the moment.

BTW, if you aren't using LinqPad yet to see what SQL gets generated, etc...I highly recommend it.

http://www.linqpad.net/






int[] nums = new int[] {1,2};
 

var q = from o in CommercialOrgs

		join ms in Orgs_MarketSectors.Where(ms => nums.Contains(ms.OrgID))

		on o.OrgId equals ms.OrgID

		select  new { ms.OrgID, Org = o.OrgId } ;
 
 

SELECT [t1].[OrgID], [t0].[OrgId] AS [Org]

FROM [CommercialOrg] AS [t0]

INNER JOIN [Orgs_MarketSector] AS [t1] ON [t0].[OrgId] = [t1].[OrgID]

WHERE [t1].[OrgID] IN (@p0, @p1)

-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]

-- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [2]

-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1

Open in new window

0
 

Author Comment

by:j055
ID: 22833443
LINQpad looks useful, thanks.

I'm starting to get the hang of things a bit now. Would you mind just telling how to write this as a Lambda expression? Is it possible when adding another 'from'?

Thanks
Andrew


query = from o in query

    from a in o.Orgs_Activities

    where actIDs.Contains(a.ID)

    select o;

Open in new window

0
 
LVL 4

Expert Comment

by:novynov
ID: 22833726
What part of the query are you looking to have as a lambda expression? Other than not having the join expression, the query looks fine. Are you having a specific problem...or am I not understanding the question?
0
 

Author Comment

by:j055
ID: 22839220
I've got the code producing the results I require now. I'm trying to clean it up a bit now. it looks to me that I could be more efficient but the LINQ syntax is still a little confusing to me. for example I have code written like this:

...see snippet

I just want to know if I can simplify/optimise the way it is written.
            if (!string.IsNullOrEmpty(searchData.Organisation))

            {

                query = query.Where(o => o.Organisation.Contains(searchData.Organisation));

            }
 

//and
 

            if (!string.IsNullOrEmpty(searchData.ContactName))

            {

                string[] names = searchData.ContactName.Split(new char[] { ',', ' ' }, StringSplitOptions.RemoveEmptyEntries);
 

                query = from o in query

                        from c in o.OrgContacts

                        where names.Contains(c.LastName)

                        select o;

            }

Open in new window

0
 
LVL 4

Accepted Solution

by:
novynov earned 250 total points
ID: 22839561
Is the first query supposed to be like the second (i.e. the IN generating type)? If so, I'm not sure the query is written correctly...unless you can only have one Organisation value.

I also notice that you mix the 2 different LINQ syntaxes in each of your Where statements. This is legal, and sometimes necessary...but probably not so in this case. However, that's just a style thing. See snippet below for alternative.

I don't see anything else that can be done to simplify much. Both linq queries are fairly simple. If they are easy to read/maintain, and produce the correct results with generated SQL that is efficient, I think you're good. In fact, although you could probably write both as one query, it would be far less readable. One of the beauties of LINQ is our ability to "compose" queries in it without compromising performance - due to delayed expression tree parsing and execution.

Was there a specific are of the query that you don't like?

query = from o in query

        where o.Organisation.Contains(searchData.Organization)

        select o;

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Summary Displaying images in RichTextBox is a common requirement with limited solutions available. Pasting through clipboard or embedding into RTF content only support static images.  This article describes how to insert Windows control objects int…
A basic question.. “What is the Garbage Collector?” The usual answer given back: “Garbage collector is a background thread run by the CLR for freeing up the memory space used by the objects which are no longer used by the program.” I wondered …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

914 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now