Filter List based on another List using Linq query in Entity Framework

nauna

SOLVED CLOSED ACTIVE ANSWERED
User: nauna
Posted: on Jul 10, 2019 07:26 AM
Forum: Entity Framework
Answer: 1
Views: 13313
Sample Code: Download

i have this query which returns list works fine

var q = (from p in db.Profiles
         join ce in db.CoachExpertises on p.UserName equals ce.UserName
         select new { 
         username=p.UserName,
         FirstName=p.FirstName,
         LastName=p.LastName,
         Avatar=p.Avatar,
         Summary=ce.Summary,
         Introduction=ce.Introduction,
         Country=p.Country,
         Degrees=ce.Degrees.Replace(","," | "),
         videolink =ce.PresentationLink,
         Languages=p.Language.Replace(",", " | ")          
         });

then here if query string value is not null so am running query which returns only user name

string expertise = "";
if (this.RouteData.Values["Expertise"] != null)
{
    expertise = this.RouteData.Values["Expertise"].ToString();
 
    var e = (from ue in db.UserExpertises
             join ex in db.Expertises on ue.ExpertiseId equals ex.ExpertiseId
             where ex.Urlpath == expertise.ToString()
             select new
             {
                 Fusername = ue.UserName
             });

then i want to loop second query e and pass the username to first query q so its filter the data based on user name

    //here i want to filter data based on above query
    foreach (var item in e)
    { 
        //pass filter data to q as list
        q = q.Where(u => u.username == item.Fusername);
    }

for e.g

firs query retursn following value

second quer e runs returns only

so e values A and B should passs to first query make it inner join and first query now should onlyu return A and B

pandeyis...

0 4

Reply
ANSWER
User: pandeyism
Replied: on Jul 11, 2019 02:17 AM
Report

Hi nauna,

Refer below sample.

Database

For this sample I have used of NorthWind database that you can download using the link given below.

Download Northwind Database

HTML

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="CategoryID" HeaderText="CategoryID" />
        <asp:BoundField DataField="CategoryName" HeaderText="CategoryName" />
        <asp:BoundField DataField="ProductName" HeaderText="ProductName" />
    </Columns>
</asp:GridView>

Namespaces

using NorthwindModel;

VB.Net

Imports NorthwindModel

Code

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        NorthwindEntities entity = new NorthwindEntities();
        var product = (from prod in entity.Products
                        select prod).ToList();


        var category = (from cate in entity.Categories
                        where cate.CategoryID == 1 || cate.CategoryID == 2
                        select cate).ToList();

        var result = from p in product
                        join c in category on p.CategoryID equals c.CategoryID
                        select new
                        {
                            CategoryID = p.CategoryID,
                            CategoryName = c.CategoryName,
                            ProductName = p.ProductName
                        }; ;

        GridView1.DataSource = result.ToList().Take(5);
        GridView1.DataBind();
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Dim entity As NorthwindEntities = New NorthwindEntities()
        Dim product = (From prod In entity.Products Select prod).ToList()
        Dim category = (From cate In entity.Categories Where cate.CategoryID = 1 OrElse cate.CategoryID = 2 Select cate).ToList()
        Dim result = From p In product Join c In category On p.CategoryID Equals c.CategoryID Select New With {Key .CategoryID = p.CategoryID, Key .CategoryName = c.CategoryName, Key .ProductName = p.ProductName}
        GridView1.DataSource = result.ToList().Take(5)
        GridView1.DataBind()
    End If
End Sub

Screenshot

Filter List based on another List using Linq query in Entity Framework

Company

Explore

Follow us

Contact Us