CODEWITHSUNDEEP
c#performancelinq
Mithrilhall
Mithrilhall

Reputation: 1502

Excluding items using linq

I have two lists being returned via LINQ. My goal is to return all the items from the 'defaults' list minus what is in the 'notDefaults' list.

I'm currently doing this via two nested for loops and using 'RemoveAt'. Could this be done with only one LINQ statement? Basically, I need everything in the 'defaults' list unless the item exists in the table 'AssetPayrollMarkupOverrides'.

I'm apprehensive about using 'RemoveAt' since it appears to be slow.

var defaults = (from a in dbcontext.Assets
    join b in dbcontext.AssetAddresses on a.AssetID equals b.AssetID
    join c in dbcontext.Addresses on b.AddressID equals c.AddressID
    join d in dbcontext.StateLookups on c.StateID equals d.StateID
    where d.ShortName == state
    select new AssetMarkup()
    {
        AssetId = a.AssetID,
        AssetName = a.AssetName,
        Seg1_Code = a.Seg1_Code,
    }).ToList();


var notDefaults = (from a in dbcontext.Assets
    join b in dbcontext.AssetAddresses on a.AssetID equals b.AssetID
    join c in dbcontext.Addresses on b.AddressID equals c.AddressID
    join d in dbcontext.StateLookups on c.StateID equals d.StateID
    join e in dbcontext.AssetPayrollMarkupOverrides on a.AssetID equals e.AssetID
    where d.ShortName == state
    select new AssetMarkup()
    {
        AssetId = a.AssetID,
        AssetName = a.AssetName,
        Seg1_Code = a.Seg1_Code,
        ShortName = d.ShortName,
        OfficePercentage = e.OfficePercentage,
        MaintenancePercentage = e.MaintenancePercentage,
        Note = e.Note
    }).ToList();

I originally had it this way to make sure the two lists matched:

var defaults = (from a in dbcontext.Assets
    join b in dbcontext.AssetAddresses on a.AssetID equals b.AssetID
    join c in dbcontext.Addresses on b.AddressID equals c.AddressID
    join d in dbcontext.StateLookups on c.StateID equals d.StateID
    where d.ShortName == state
    select new AssetMarkup()
    {
        AssetId = a.AssetID,
        AssetName = a.AssetName,
        Seg1_Code = a.Seg1_Code,
        ShortName = d.ShortName,
        OfficePercentage = dbcontext.PayrollMarkups.Where(x => x.StateID == c.StateID).Select(x => x.OfficePercentage).FirstOrDefault(),
        MaintenancePercentage = dbcontext.PayrollMarkups.Where(x => x.StateID == c.StateID).Select(x => x.MaintenancePercentage).FirstOrDefault(),
        Note = dbcontext.AssetPayrollMarkupOverrides.Where(x => x.AssetID == a.AssetID).Select(x => x.Note).FirstOrDefault()
    }).ToList();

var notDefaults = (from a in dbcontext.Assets
   join b in dbcontext.AssetAddresses on a.AssetID equals b.AssetID
   join c in dbcontext.Addresses on b.AddressID equals c.AddressID
   join d in dbcontext.StateLookups on c.StateID equals d.StateID
   join e in dbcontext.AssetPayrollMarkupOverrides on a.AssetID equals e.AssetID
   where d.ShortName == state
   select new AssetMarkup()
   {
       AssetId = a.AssetID,
       AssetName = a.AssetName,
       Seg1_Code = a.Seg1_Code,
       ShortName = d.ShortName,
       OfficePercentage = dbcontext.PayrollMarkups.Where(x => x.StateID == c.StateID).Select(x => x.OfficePercentage).FirstOrDefault(),
       MaintenancePercentage = dbcontext.PayrollMarkups.Where(x => x.StateID == c.StateID).Select(x => x.MaintenancePercentage).FirstOrDefault(),
       Note = dbcontext.AssetPayrollMarkupOverrides.Where(x => x.AssetID == a.AssetID).Select(x => x.Note).FirstOrDefault()
   }).ToList();

return Json(defaults.Except(notDefaults).OrderBy(x => x.AssetName).ToDataSourceResult(request));

Upvotes: 0

Views: 214

Answers (3)

Neil Mountford
Neil Mountford

Reputation: 2001

What you're looking for is the complement of the two sets. The Linq Except() method is designed to do this (you could use the overload that takes an IEqualityComparer):

public class AssetMarkupComparer : IEqualityComparer<AssetMarkup>
{
    public bool Equals(AssetMarkup am1, AssetMarkup am2)
    {
        return am1.AssetId == am2.AssetId;
    }

    public int GetHashCode(AssetMarkup obj)
    {
        return obj.AssetId.GetHashCode();
    }
}

var complement = defaults.Except(notDefaults, new AssetMarkupComparer());

You'd need to account for nulls in the comparer methods but this is just a skeleton example.

Upvotes: 1

Jozef Benikovsk&#253;
Jozef Benikovsk&#253;

Reputation: 1141

var notDefaultIds = notDefaults.Select(nd => nd.AssetID);
var result = defaults.Where(def => !notDefaultIds.Any(id => id == def.AssetID);

If you want to reduce db roundtrips, move ToList() from notDefaults, defaults to result;

Upvotes: 0

Dave Bish
Dave Bish

Reputation: 19646

You can use Except

var remaining = defaults.Except(notDefaults);

You may need to ensure your equality comparer on AssetMarkup is set to compare the objects in a non reference-based way.

See here http://msdn.microsoft.com/en-us/library/ms173147(v=vs.80).aspx

Upvotes: 4

Related Questions