ODATA query: how to filter main table records based on child table data

See more:

OData

C#6.0

WebAPI2

Hi,

I would like to filter the records based on child table data using odata query. Example I have tables like below. Employee contains empId and Name, EmployeeText is having empId, languageid and Text

Employees
EmpId Name
1 Test
2 Test2

EmployeeText
empId LanguageId Text
1 1 Test
1 2 French Test
2 1 Test2
2 2 French Test2

Im using webapi with OData and Entity framework

Im calling webapi method from client side using http. I need to get the details based on Text I have given as input. For example if I give input as French Test then it should return the result like shown in below. Means based on text table text it should return main table records.

My API is having code like below

public IQueryable<employee> GetEmployee()
{
return DataContext.Employees();
}

What I have tried:

I tried a query like below, but it is filtering only child records as we applied for child table. Not sure how to meet this. Can you guys help me on this.

localhost:12345/odata/employees?$expand(EmployeeText($filter=Text eq 'French Test'))

I got results like below:

{
  "empId": 1,
  "Text" : "Test",
  "EmployeeText":[
   {
     "empId":1,
     "LanguageId":2,
     "Text": "French Test"
   },
  ]
},
{
  "empId": 2,
  "Text" : "Test",
  "EmployeeText":[]
}

My result should be like below

{
  "empId": 1,
  "Text" : "Test",
  "EmployeeText":[
   {
     "empId":1,
     "LanguageId":2,
     "Text": "French Test"
   },
  ]
}

Please let me know if I am not clear.

Thanks in advance!!