ODATA查询：如何根据子表数据过滤主表记录

问题描述

我想使用odata查询根据子表数据过滤记录。示例I具有如下表格。 Employee包含empId和Name，EmployeeText有empId，languageid和text



员工

EmpId名称

1测试

2测试2



EmployeeText

empId LanguageId Text

1 1测试

1 2法语测试

2 1测试2

2 2法语测试2



我使用带有OData和实体框架的webapi



我使用http从客户端调用webapi方法。我需要根据我作为输入提供的文本获取详细信息。例如，如果我将输入作为法语测试，那么它应该返回如下所示的结果。基于文本表文本的方法应该返回主表记录。



我的API代码如下



public IQueryable< employee> GetEmployee（）

{

返回DataContext.Employees（）;

}



我尝试了什么：



我尝试过如下查询，但是我们申请子表时只过滤子记录。不知道如何满足这一要求。你能帮我解决这个问题。



localhost：12345 / odata / employees？$ expand（EmployeeText（$ filter = Text eq'French Test'）） />


我得到如下结果：

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!!

推荐答案

{
  "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!!

这篇关于ODATA查询：如何根据子表数据过滤主表记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！