How to format linq to replace AND with OR when combining multiple Where's

user_6236008 0
Viewed 224

I have a Asp.net MVC C# app that contains a search page with the following fields: Search.cshtml

The input values can contain only portions eg. Carnet Number could be 'Wakka 12' and in the database there is a 'Wakka 1234' record that must be returned. In SQL it would be Where [CarnetNumber] like '%Wakka 123%'

Further the fields must be used in typical OR combinations eg. if the user enters a value in the Carnet Number field and a value in the Holder field, the result should be all records that have Carnet Numbers that contains the Carnet Number entered OR records where the Holder field contains the Holder input value - SQL Where [CarnetNumber] like '%Wakka 123%' OR [Holder] like '%some holder%'

Fields that have no supplied value should not be added to the query.

My difficulty is getting the OR part working in linq. I have looked at Dynamic linq and expressions but am unable to get it to work.

This is the closest I got to a solution so far:

        var Q = (from Cx in db.Carnets
             where Cx.CarnetNumber.Contains(carnet.CarnetNumber)
             || Cx.Holder.Contains(carnet.Holder)
             || Cx.ImportSerial.Contains(carnet.ImportSerial)
             || Cx.CPDRegistrationNumber.Contains(carnet.CPDRegistrationNumber)
             || Cx.CPDChassis.Contains(carnet.CPDChassis)
             || Cx.CPDEngine.Contains(carnet.CPDEngine)
             || Cx.CPDMake.Contains(carnet.CPDMake)
             select Cx);

This gives me a SQL query :

SELECT 
[Extent1].[CarnetNumber] AS [CarnetNumber], 
[Extent1].[CarnetType] AS [CarnetType], 
[Extent1].[Holder] AS [Holder], 
[Extent1].[ValidUntillDate] AS [ValidUntillDate], 
[Extent1].[IssuingAuthority] AS [IssuingAuthority], 
[Extent1].[Currency] AS [Currency], 
[Extent1].[Value] AS [Value], 
[Extent1].[PortOfImport] AS [PortOfImport], 
[Extent1].[ImportSerial] AS [ImportSerial], 
[Extent1].[ImportDate] AS [ImportDate], 
[Extent1].[PortOfExport] AS [PortOfExport], 
[Extent1].[ExportSerial] AS [ExportSerial], 
[Extent1].[DateOfExport] AS [DateOfExport], 
[Extent1].[CarnetItems] AS [CarnetItems], 
[Extent1].[CPDRegistrationNumber] AS [CPDRegistrationNumber], 
[Extent1].[CPDChassis] AS [CPDChassis], 
[Extent1].[CPDEngine] AS [CPDEngine], 
[Extent1].[CPDMake] AS [CPDMake], 
[Extent1].[CPDTransportType] AS [CPDTransportType], 
[Extent1].[AcquittalStatus] AS [AcquittalStatus], 
[Extent1].[LastDateProcessed] AS [LastDateProcessed], 
[Extent1].[LastAccessedByUser] AS [LastAccessedByUser]
FROM [dbo].[Carnet] AS [Extent1]
WHERE ([Extent1].[CarnetNumber] LIKE @p__linq__0 ESCAPE N'~') 
OR ([Extent1].[Holder] LIKE @p__linq__1 ESCAPE N'~') 
OR ([Extent1].[ImportSerial] LIKE @p__linq__2 ESCAPE N'~') 
OR ([Extent1].[CPDRegistrationNumber] LIKE @p__linq__3 ESCAPE N'~') 
OR ([Extent1].[CPDChassis] LIKE @p__linq__4 ESCAPE N'~') 
OR ([Extent1].[CPDEngine] LIKE @p__linq__5 ESCAPE N'~') 
OR ([Extent1].[CPDMake] LIKE @p__linq__6 ESCAPE N'~')

Now if I run this in SQL and replace the values (@p_linq_#) I get nothing back because the other fields that were not supplied are NULL. SQL is doing a LIKE null test.

I tried separating the .Where clause but the OR changed into AND's:

string search = "";

var Q = db.Carnets
    .Where(a => a.CarnetNumber != null);

if (carnet.CarnetNumber != null)
{
    search = carnet.CarnetNumber.Trim();
    Q = Q.Where(a => a.CarnetNumber.Contains(search));
}

if (carnet.Holder != null)
{
    search = carnet.Holder.Trim();
    Q = Q.Where(a => a.Holder.Contains(search));
}

This gives SQL Where :

FROM [dbo].[Carnet] AS [Extent1]
WHERE ([Extent1].[CarnetNumber] LIKE @p__linq__0 ESCAPE N'~') 
AND ([Extent1].[Holder] LIKE @p__linq__1 ESCAPE N'~')

If I add .TRIM() in C# the SQL Where goes bonkers and converts it into a CHARINDEX query, hence the search var.

I think the first attempt is closer to home but how do I remove all fields in the Where part that contains a Null field from my linq?

model-view-controller linq c#
1 Answers

so your problem is as you already mentioned that you are performing checks on values that are null.

in your case you should check if these values you want to compare on are not null or just a whitespace before comparing them.

this should be an attempt for you

    var Q = (from Cx in db.Carnets
                 where 
                    (!string.IsNullOrWhitespace(carnet.CarnetNumber) && Cx.CarnetNumber.Contains(carnet.CarnetNumber))
                 || (!string.IsNullOrWhitespace(carnet.Holder) && Cx.Holder.Contains(carnet.Holder))
                 || (!string.IsNullOrWhitespace(carnet.ImportSerial) && Cx.ImportSerial.Contains(carnet.ImportSerial))
                 || (!string.IsNullOrWhitespace(carnet.CPDRegistrationNumber) && Cx.CPDRegistrationNumber.Contains(carnet.CPDRegistrationNumber))
                 || (!string.IsNullOrWhitespace(carnet.CPDChassis) && Cx.CPDChassis.Contains(carnet.CPDChassis))
                 || (!string.IsNullOrWhitespace(carnet.CPDEngine) && Cx.CPDEngine.Contains(carnet.CPDEngine))
                 || (!string.IsNullOrWhitespace(carnet.CPDMake) && Cx.CPDMake.Contains(carnet.CPDMake))
                 select Cx);

if Linq cannot translate string.IsNullOrWhitespace() function you have to make the check outside of the actual query. So you basically build up your sql query dependend on several if's and in the end you execute that query.

try this

var Q = from Cx in db.Carnets select Cx;

if (!string.IsNullOrWhitespace(carnet.CarnetNumber))
{
    var withCarnetNumber = from Cx in Q
        where Cx.CarnetNumber.Contains(carnet.CarnetNumber)
        select Cx;
}

if (!string.IsNullOrWhitespace(carnet.Holder))
{
    var withCarnetHolder = from Cx in Q
        where Cx.CarnetHolder.Contains(carnet.Holder)
        select Cx;
}

...

var result = withCarnetNumber.Union(withCarnetHolder).Union(...).ToList();
user_78436850
Related