CODEWITHSUNDEEP
c#linq
Derek
Derek

Reputation: 8628

C# LINQ - Filter Queries

I'm very new to LINQ and I'm looking for some guidance in the best route to take on my project.

I'm developing an application that sends notification messages, via email or SMS.

I currently have 3 tables in my SQL database that I grab in c# :-

        var contacts = db.GetTable<Contact>();
        var distributionLists = db.GetTable<DistributionList>();
        var JunctionTable = db.GetTable<ContactsDistribution>();

Users can select multiple distribution lists from a listbox control. I need to find to figure a way of adding records to a list/object for each selected Item. Once I have the final data, I need to remove any duplicates, as a Contact can belong in multiple Distribution Lists.

In the end, there should be only 1 entry for each contact in the list, as I don't want to send the same notification to 1 contact multiple times.

The LINQ Query i have is below :-

foreach (var li in listBox1.SelectedItems)
        {
            string text = "";
            text += li.ToString();


            var InitialList = (from j in JunctionTable
                  where j.DistributionName.Equals(text)
                  join c in contacts
                  on j.ContactID equals c.ContactID
                  select new { c.ContactID,  j.DistributionName }).ToList();

        }

I need to keep adding to this Query for each selected Item in the Listbox. I did try using a List object, but I'm not sure it is the correct way to do this.

Once I have all entries.. I can then remove duplicates, but not sure if I'm going down the right path with this??

Is there a better approach?

Upvotes: 2

Views: 1388

Answers (2)

Cyril Gandon
Cyril Gandon

Reputation: 17048

Don't forget to cut into small piece your code!

If it is fast enough, and for readibility, I choose this solution :

var contacts = db.GetTable<Contact>();
var distributionLists = db.GetTable<DistributionList>();
var JunctionTable = db.GetTable<ContactsDistribution>();

var distributionNames = listBox1.SelectedItems
                          .Cast<object>()
                          .Select(t => t.ToString());

var junctions = JunctionTable.Where(j => distributionNames.Contains(j.DistributionName));

var contactIds = junctions.Select(j => j.ContactID).Distinct();

var contacts = contacts.Where(c => contactIds.Contains(c.ContactID));

Upvotes: 2

david.s
david.s

Reputation: 11403

I don't exactly understand what you want to do, but this might help you a bit:

var listBoxItems = listBox1.SelectedItems.Select(x => x.ToString());

Alternatively:

var listBoxItems = from li in listBox1.SelectedItems
                   select new { x.ToString() };

var InitialList = (from j in JunctionTable
                   where listBoxItems.Contains(j.DistributionName)
                   join c in contacts on j.ContactID equals c.ContactID
                   select new { c.ContactID,  j.DistributionName }).ToList();

If you need to remove duplicates from InitialList you can use the Distinct method:

var InitialList = (from j in JunctionTable
                   where listBoxItems.Contains(j.DistributionName)
                   join c in contacts on j.ContactID equals c.ContactID
                   select new { c.ContactID,  j.DistributionName }).Distinct().ToList();

Upvotes: 4

Related Questions