I want to build an advanced multiple parameters search for a website.
This is my DBML and ORM schema:
enter image description here In this advanced multiple parameters search user can search through Estates with multiple parameters as like size, floor, city, price and so on.
This is the function that I coded to handle this part.

 private DataTable linq_search_by_details()
    {
        myDBMLDataContext ctx = new myDBMLDataContext(address);
        var query = ctx.Estates.AsQueryable();
        query = query.Where(c => c.eshId == int.Parse(ddlEshape.SelectedValue.ToString()));
        query = query.Where(c => c.cityId == int.Parse(ddlcity.SelectedValue.ToString()));
        query = query.Where(c => c.ETId == int.Parse(ddlType.SelectedValue.ToString()));
        query = query.Where(c => c.dealingId == int.Parse(ddldeal.SelectedValue.ToString()));
        query = query.Where(c => c.deedId == int.Parse(ddldeed.SelectedValue.ToString()));
        if (!string.IsNullOrEmpty(txtPrepaymentFrom.Value.Trim()))
        {
            query = query.Where(c => int.Parse(c.prepayment) <= int.Parse(txtPrepaymentFrom.Value));
        }
        if (!string.IsNullOrEmpty(txtPrepaymentTo.Value.Trim()))
        {
            query = query.Where(c => int.Parse(c.prepayment) >= int.Parse(txtPrepaymentTo.Value));
        }
        if (!string.IsNullOrEmpty(txtPrepaymentFrom.Value.Trim()) && !string.IsNullOrEmpty(txtPrepaymentTo.Value.Trim()))
        {
            query = query.Where(c => int.Parse(c.prepayment) <= int.Parse(txtPrepaymentFrom.Value) && int.Parse(c.prepayment) >= int.Parse(txtPrepaymentTo.Value));
        }

        if (!string.IsNullOrEmpty(txtPriceFrom.Value.Trim()))
        {
            query = query.Where(c => int.Parse(c.price) <= int.Parse(txtPriceFrom.Value));
        }
        if (!string.IsNullOrEmpty(txtPriceTo.Value.Trim()))
        {
            query = query.Where(c => int.Parse(c.price) >= int.Parse(txtPriceTo.Value));
        }
        if (!string.IsNullOrEmpty(txtPriceFrom.Value.Trim()) && !string.IsNullOrEmpty(txtPriceTo.Value.Trim()))
        {
            query = query.Where(c => int.Parse(c.price) <= int.Parse(txtPriceFrom.Value) && int.Parse(c.price) >= int.Parse(txtPriceTo.Value));
        }

        if (!string.IsNullOrEmpty(txtFloor.Value.Trim()))
        {
            query = query.Where(c => c.eFloor == short.Parse(txtFloor.Value));
        }
        if (chbExchange.Checked)
        {
            query = query.Where(c => c.exchange == true);
        }

        var final = query.Select(c => new { c.esId,c.owId, c.City.cityName, c.EstateShape.eshName, c.EstateType.ETName, c.owner.owFname, c.owner.owLname, c.esSize, c.prepayment, c.price });
        return Special.LINQResultToDataTable(final.ToList());
}

This function works perfectly but now I want more to add some parameres from EstateEquipment and EstateFacility.
As you can see in ORM the relation between Estate and EstateEquipment (also Estate and EstateFacility) is one to many.
Now I want to user can search through Estate in cityId = 1, size around 400m which has for example eqId = 1 and 2 from EstateEquipment then which has for example fId = 1 and 2 from EstateFacility.

This is how I tried to handle last part.

foreach (ListItem item in cblEquipment.Items)
{
    if (item.Selected)
    {
        eq = true;
    }
}
if(eq)
{
    var eqQuery = ctx.EstateEquipments.AsQueryable();
    foreach (ListItem item in cblEquipment.Items)
    {
        if (item.Selected)
        {
            eqQuery = eqQuery.Where(c => c.eqId == int.Parse(item.Value.ToString()));
        }
    }
    var eqFinal = eqQuery.Select(c => new { c.Estate.esId, c.Estate.owner.owId, c.Estate.City.cityName, c.Estate.EstateShape.eshName, c.Estate.EstateType.ETName, c.Estate.owner.owFname, c.Estate.owner.owLname, c.Estate.esSize, c.Estate.prepayment, c.Estate.price });
    DataTable dtEq = Special.LINQResultToDataTable(eqFinal.ToList());
    if(dtEq.Rows.Count>0)
    {
        this.Build_search(dtEq);
    }
    else
    {
        msg = "No record found";
        sysMsg.Attributes["class"] = "";
        sysMsg.Attributes["class"] = "alert alert-warning";
    }
}

Here, First I check which equipment is selected from the list. Then extended the query accordance with Items selected. But I don't know how to join this new result with last result and even EstateFacility result.
Thanks for any help.

Related posts

Recent Viewed