3

I'm using Entity Framework as my data provider. And there is one specific table did not generate sql statement right. While I have passed query condition, but the Entity Framework still generate sql statement for the whole table.

The Code is like this:

public IList<Device> GetDeviceByNodeId(string nodeId)
        => GetModels(device => device.DeviceNodeId == nodeId).ToList();

public virtual IEnumerable<T> GetModels(Func<T, bool> exp)
        => EntitySet.Where(exp);

And the generated sql statement is like :

SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[DeviceTypeId] AS [DeviceTypeId], 
[Extent1].[OriginalDeviceId] AS [OriginalDeviceId], 
[Extent1].[DeviceCode] AS [DeviceCode], 
[Extent1].[StatCode] AS [StatCode], 
[Extent1].[DevicePassword] AS [DevicePassword], 
[Extent1].[DeviceModuleGuid] AS [DeviceModuleGuid], 
[Extent1].[DeviceNodeId] AS [DeviceNodeId], 
[Extent1].[FirmwareSetId] AS [FirmwareSetId], 
[Extent1].[ProjectId] AS [ProjectId], 
[Extent1].[StartTime] AS [StartTime], 
[Extent1].[PreEndTime] AS [PreEndTime], 
[Extent1].[EndTime] AS [EndTime], 
[Extent1].[Status] AS [Status], 
[Extent1].[CameraId] AS [CameraId], 
[Extent1].[DomainId] AS [DomainId], 
[Extent1].[CreateDateTime] AS [CreateDateTime], 
[Extent1].[CreateUserId] AS [CreateUserId], 
[Extent1].[LastUpdateDateTime] AS [LastUpdateDateTime], 
[Extent1].[LastUpdateUserId] AS [LastUpdateUserId], 
[Extent1].[IsDeleted] AS [IsDeleted], 
[Extent1].[IsEnabled] AS [IsEnabled]
FROM [dbo].[Devices] AS [Extent1]

Did I use linq the wrong way ?

iggymoran
  • 4,059
  • 2
  • 21
  • 26
Autyan
  • 111
  • 1
  • 12

1 Answers1

3

It is because you are using a function, and not an expression.

Remember that IQueryables are also IEnumerables, so you are really calling Enumerable.Where, not Queryable.Where. This will cause Entity Framework to execute the query and filter the results in memory.

Change your method signature from:

public virtual IEnumerable<T> GetModels(Func<T, bool> exp)

to

public virtual IEnumerable<T> GetModels(Expression<Func<T, bool>> exp)

See this post for more information about the differences between Expressions and Functions.

Community
  • 1
  • 1
iggymoran
  • 4,059
  • 2
  • 21
  • 26