CODEWITHSUNDEEP
asp.netlinq
jmgibbo
jmgibbo

Reputation: 97

SQL data context with inner join

Sorry this may be blatantly obvious but I have spent all morning trying to get this working.

I am using ASP.net and have a data context as you can see below. What I want to do is add an inner join and use the company name as the search parameter rather than the deal description but for the life of me I cannot do this.

What I am trying to ultimately achieve is this:

SELECT *
FROM Deals
INNER JOIN Companies
ON Deals.CompanyID=Companies.CompanyID

Can someone please help me by rewriting the below to get the same results?

MobileVocherDataContext db = new MobileVocherDataContext();

List<Deal> dealresult= db.Deals.Where(c=>c.DealDescription.ToLower().Contains(keyword.ToLower()) && c.ExpiredDate.Date>=DateTime.Now.Date && c.Status==true).Skip(_skip).Take(_count).ToList();
List<CustomSearchresult> result = new List<CustomSearchresult> ();

Upvotes: 1

Views: 1480

Answers (2)

Spikeh
Spikeh

Reputation: 3705

I assume this is Linq to Entities?

Although you should be aware of what's going on in the background, you don't need to worry about being explicit with joins in EF... unless performance is a major issue.

You can iterate through dealresult and access Companies as follows:

foreach(var deal in dealresult) {
     var company = deal.Company;
}

Re performance: this will execute an additional SQL statement per iteration. You can get around this by using the Include() operator (this should work in EF5 - please look it up in previous versions):

 MobileVocherDataContext db = new MobileVocherDataContext();

 var dealresult = db.Deals
    .Where(d => d.DealDescription.ToLower().Contains(keyword.ToLower())
    && d.ExpiredDate.Date >= DateTime.Now.Date
    && d.Status
    && d.Company.CompanyName == "Keyword")
    .Include(c => c.Company)
    .Skip(_skip)
    .Take(_count)
    .ToList();

Upvotes: 1

Jive Boogie
Jive Boogie

Reputation: 1265

Here is some dummied up data and an example that may help you out. 

 var Custs = new List<Cust>() { new Cust() {CustId=1, Name="Joe"}, new Cust() {CustId=2, Name="Pete"}, new Cust() {CustId=3, Name="Steve"}};
 var Deals = new List<Deal>() { new Deal() {CustId=1, Name="JoeDeal"}, new Deal() {CustId=1, Name="AnotherJoeDeal"}, new Deal() {CustId=3, Name="SteveDeal"}};

var CustomerDeals = from d in Deals
                     join c in Custs on d.CustId equals c.CustId
                     select d;

foreach (var d in CustomerDeals)
{
    Console.WriteLine(d.Name);
}

Upvotes: 1

Related Questions