What is the syntax for an inner join in LINQ to SQL?

Question

i have timer implemented with boost (144 -> newest lib). This timer is started within a boos thread. The timer also starts a thread in which an io_service hosts deadline timer until thread is terminated. so a continuous timer. The boost thread which needs the timer is create from within DLL.

The DLL ExitInstance function is called, when the hosting App. unloads the DLL. The ExitInstance function stops the thread and before timer. But the io_service destruction in my timer never returns, so the app hangs.

This happens NOT, when i am able to call a Dispose Function before ExitInstance is called. However, some application loading my DLL, give not the chance to call this expose function.

Anyone know, how to work around this problem ?

Here is the code of my timer core. It is the thread which restarts the service until thread is stopped. The thread get's stopped by setting _stop flag and setting deadlime timer to 1 ms in future. summary: hangs when within Dll::ExitInstance destructed. Hangs not, if destrcuted before Dll::ExitInstance Thank you

void tcTimerThread::timerLoop(void)
{
        _running=true;

        /// create asynch service
        _io_service = new boost::asio::io_service;

        /// create timer
        _pTimer = new boost::asio::deadline_timer(*_io_service);
        while(_stop==false)
        {   
            _pTimer->expires_from_now(boost::posix_time::milliseconds(_delay));            
            /// bind timer event function
            _pTimer->async_wait(boost::bind(&tcTimerThread::timerEvent,this,boost::asio::placeholders::error));     
            try
            {
                if(_stop==false)
                {
                    /// reset async
                    _io_service->reset();
                    /// wait for timer event
                    _io_service->run(); 
                }
            }
            catch(...)
            {
            }
        }
        try
        {
           _io_service->stop();
           delete _pTimer;
           delete _io_service;
        }
        catch(...)
        {
        }
        _running=false;
}

Solution 1

It goes something like:

from t1 in db.Table1
join t2 in db.Table2 on t1.field equals t2.field
select new { t1.field2, t2.field3}

It would be nice to have sensible names and fields for your tables for a better example. :)

Update

I think for your query this might be more appropriate:

var dealercontacts = from contact in DealerContact
                     join dealer in Dealer on contact.DealerId equals dealer.ID
                     select contact;

Since you are looking for the contacts, not the dealers.

Solution 2

And because I prefer the expression chain syntax, here is how you do it with that:

var dealerContracts = DealerContact.Join(Dealer, 
                                 contact => contact.DealerId,
                                 dealer => dealer.DealerId,
                                 (contact, dealer) => contact);

Solution 3

To extend the expression chain syntax answer by Clever Human:

If you wanted to do things (like filter or select) on fields from both tables being joined together -- instead on just one of those two tables -- you could create a new object in the lambda expression of the final parameter to the Join method incorporating both of those tables, for example:

var dealerInfo = DealerContact.Join(Dealer, 
                              dc => dc.DealerId,
                              d => d.DealerId,
                              (dc, d) => new { DealerContact = dc, Dealer = d })
                          .Where(dc_d => dc_d.Dealer.FirstName == "Glenn" 
                              && dc_d.DealerContact.City == "Chicago")
                          .Select(dc_d => new {
                              dc_d.Dealer.DealerID,
                              dc_d.Dealer.FirstName,
                              dc_d.Dealer.LastName,
                              dc_d.DealerContact.City,
                              dc_d.DealerContact.State });

The interesting part is the lambda expression in line 4 of that example:

(dc, d) => new { DealerContact = dc, Dealer = d }

...where we construct a new anonymous-type object which has as properties the DealerContact and Dealer records, along with all of their fields.

We can then use fields from those records as we filter and select the results, as demonstrated by the remainder of the example, which uses dc_d as a name for the anonymous object we built which has both the DealerContact and Dealer records as its properties.

Solution 4

var results = from c in db.Companies
              join cn in db.Countries on c.CountryID equals cn.ID
              join ct in db.Cities on c.CityID equals ct.ID
              join sect in db.Sectors on c.SectorID equals sect.ID
              where (c.CountryID == cn.ID) && (c.CityID == ct.ID) && (c.SectorID == company.SectorID) && (company.SectorID == sect.ID)
              select new { country = cn.Name, city = ct.Name, c.ID, c.Name, c.Address1, c.Address2, c.Address3, c.CountryID, c.CityID, c.Region, c.PostCode, c.Telephone, c.Website, c.SectorID, Status = (ContactStatus)c.StatusID, sector = sect.Name };


return results.ToList();

Solution 5

You create a foreign key, and LINQ-to-SQL creates navigation properties for you. Each Dealer will then have a collection of DealerContacts which you can select, filter, and manipulate.

from contact in dealer.DealerContacts select contact

or

context.Dealers.Select(d => d.DealerContacts)

If you're not using navigation properties, you're missing out one of the main benefits on LINQ-to-SQL - the part that maps the object graph.

View more solutions

Comments

[Sam Miller]

What does the backtrace look like when you attach a debugger to your hung application?

[Mariusz]

Hi, Can you tell me please what is this part is about? Status = (ContactStatus)c.StatusID I am interested expecially in the fragment: (ContactStatus)c.StatusID Regards Mariusz

[Joel Mueller]

@aristo - looking at the code, I'm guessing that ContactStatus is really an enum, and c.StatusID isn't really an ID, but the numeric value of the enum. If I'm right, (ContactStatus)c.StatusID is really just casting an integer to an enum.

[user450195]

Well, i could add file DLL module to my project and change it to call something on case PROCESS_DETACH. But looking into code, i see for static linked MFC ExitInstance called from PROCESS_DETACH directly.So i guess this is no solution. I have the suspect there are some basic process resources already cleaned so the asio service are "confused"..?

[user450195]

The timer is stopped before CWinApp::ExitInstance (base) is called. My ExitInstance stops the timer. However, the timer, running in a simple boost thread hangs in this situation. Meanwhile i replaced the thread where timer resides by CWinThread. This seems to work fine. However, i would like to have a solution with boost. Any ideas are welcome.

[Kuntady Nithesh]

what to do when i want columns of both d & dc ?

[Tomas]

Oh man, you saved my time, I don't need to deal with these stupid joins anymore!

[Elisabeth]

@KuntadyNithesh Then return a class you have created like select new MyCustomer{ Id = dc.id, Id2 = d.id } Thats it!

[deniz]

If you need to filter or select on fields from both joined tables, instead of just on fields of one of the two tables (the DealerContact table in this answer's example), here's an example: stackoverflow.com/a/29310640/12484

[deniz]

What does a LINQ query with multiple "from" clauses (as in this example) look like in expression chain syntax? Is it possible?

[Shaiju T]

thank you, from now on i will use sensible names as best practise which makes sense in linq , instead of from c or from t1

[Gert Arnold]

Its method syntax equivalent is SelectMany().

[Mariusz]

Joins with lambdas have awful syntax. I refuse to use it ;-)

[deniz]

@aristo I don't blame you at all. I usually have to refer back to this post to remind myself of the syntax!

[Sarfraj Sutar]

var list = (from u in db.Yourfirsttablename join c in db.secondtablename on u.firsttablecommonfields equals c.secondtablecommon field where u.Username == username select new {u.UserId, u.CustomerId, u.ClientId, u.RoleId, u.Username, u.Email, u.Password, u.Salt, u.Hint1, u.Hint2, u.Hint3, u.Locked, u.Active,c.ProfilePic}).First();

[Blue]

Welcome to Stack Overflow! While this code snippet may solve the question, including an explanation really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. Please also try not to crowd your code with explanatory comments, as this reduces the readability of both the code and the explanations!

[0014]

Some people like me prefers consistency. That's why I search for the lambda syntax specifically.

[Jeroen Heier]

Welcome to Stack Overflow! This answer adds nothing to the already existing answers.

[mvanella]

I've used linq joins like many times in the past but I ran into an issue that I hadn't seen before, since the error you see in VS is not entirely clear: When you write the ON statement you must reference the FROM table first. You can say on t1.field equals t2.field but you cannot write on t2.field equals t1.field as the compiler won't understand what t2 is referring to in that case.