Solved

LINQ to SQL and dynamic left outer join

Posted on 2008-10-28
18
4,984 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Normally the drop down box control found in the .Net framework tools is able to select just one data and value at a time, which is displayed on the text area.   But what if you want to have multiple values to be selected in the drop down box? As …
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
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 video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

747 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

12 Experts available now in Live!

Get 1:1 Help Now