How to join tables using lambda expression

Question

I'm new to lambda expression and entity framework. My question is, I have following tables structure.

tbl_loan => loanID,LoanValue,LoanType

tbl_crin => crID,CRValue,CUSName,FacID // here FacID is foreign key of tbl_loan(loanID)

tbl_crg => crgID,crgName,CRValue,Amount FacID // here FacID foreign key of tbl_loan(loanID)

I need to write to take all the columns of each tables based on loanID .how to write query using lambda expression.

In this method I need to return result of that query.which accept parameter called loanID. I tried simple one,but I have no idea to write complete code(lambda expression )

public List<tbl_loan> GetCRGDetails(int loanID)
    {

        var result = new List<tbl_loan>();
        var entities = new LAEntities();
        try
        {
            result = entities.tbl_loan.Where(ln => ln.loanID == loanID).ToList();

        }
        catch (Exception e)
        {
            Logger.LogWriter("SITS.SB.LA.BL.App", e, "AgeDataLogic", "GetCRGDetails");
        }

        return result;
    }

Please help me to solve this. Here I retun tbl_loan. should I need to create additional class to retun all the data? or have any otherway to do it?

Update:

In my project, part of details.cshtml as follows,

@model SITS.SB.LA.DA.tbl_loan 

@{
    ViewBag.Title = "Details";
}

<h2>Details</h2>

<div>
    <h4>Customer Loan Details</h4>
    <hr/>

    <label for="colFormLabelSm" class="col-sm-2 col-form-label col-form-label-sm" ng-model="">@Html.DisplayFor(model => model.ID)</label>
    <label for="colFormLabelSm" class="col-sm-2 col-form-label col-form-label-sm" ng-model="">@Html.DisplayFor(model => model.NETINCOME)</label>

So, how can I display other table's columns values,such as (crID,CRValue,CUSName)

My controller class as follows,

public ActionResult Details(int id)
{
    var logic = new LoanDetailsDataLogic();
    var result = logic.GetCRGDetails(id);
    return View(result);

}

You don't need joins if you use proper relations and navigation properties. The ORM will generate the joins. Besides, the code you posted shows a LINQ to Objects query to classes with table-like names. There's no Entity Framework code in the questino — Panagiotis Kanavos, Sep 04 '18 at 09:05
Check this link https://stackoverflow.com/questions/22907820/lambda-expression-join-multiple-tables-with-select-and-where-clause — Arun Kumar, Sep 04 '18 at 09:05
@PanagiotisKanavos I'm not clear . please give me code sample. In my asp.net web application. how can I access those data to view — Sachith Wickramaarachchi, Sep 04 '18 at 09:06
as @PanagiotisKanavos said, normally you could do just `Loan.Crins` which would point to associated records from `tbl_crin`. Also, the naming `tbl_loan` etc is weird, ORM is supposed to abstract from the db relational model and work with data as entities/objects of a language - not to (what you seem to be doing) bring db stuff into the code — Vladi Pavelka, Sep 04 '18 at 09:07
@VladiPavelka please check updated question, how can I show other tables data in view — Sachith Wickramaarachchi, Sep 04 '18 at 09:15

score 0 · Answer 1 · answered Sep 04 '18 at 11:55

you can try Include Keyword of Entity Framework like.

result = entities.tbl_loan.Include("tbl_crin").Where(ln => ln.loanID == loanID).ToList();

Or you can write query like

 result =(from u in tbl_loan
   join t1 in tbl_crin on u.loanID = t1.loanID
   Join t2 in tbl_crg  on u.loanID = t2.loanID
   select new {u,t1,t2});

How to join tables using lambda expression

1 Answers1