none
LINQ) How to get data with 1 occurrence at column A when the value of column B is fixed. RRS feed

  • Question

  • customerID        Menu

         777               Pizza

         929               Pizza

           4                IceCream

         224              Cake

         429              Strawberry

        1119             Strawberry

        7979             Pizza

          99               Cake

         100              Strawberry 

    The real data is, of course, much complicated.

    By LINQ, how to get data with 1 occurrence like customerID 4, IceCream ? Or 2 occurrence like Cake ?

    Thank you so much !

    Saturday, October 26, 2019 11:08 PM

All replies

  • Hello,

    Let's say a List<T> is being used.

    public class Food
    {
        public int Id { get; set; }
        public string Item { get; set; }
    }

    Examples

    // all occurrences
    var CakeList = foodList.Where(food => food.Item == "Cake").ToList();
    if (CakeList.Count >0)
    {
        foreach (var item in CakeList)
        {
            Console.WriteLine(item.Id);
        }
    }
    
    // get first occurrence
    var singleCake = foodList.Where(food => food.Item == "Cake").FirstOrDefault();
    if (singleCake != null)
    {
        Console.WriteLine(singleCake.Id);
    }
    
    singleCake = foodList.Where(food => food.Item == "Cake").LastOrDefault();
    if (singleCake != null)
    {
        Console.WriteLine(singleCake.Id);
    }

    Using a DataTable

    var firstCake = dt.AsEnumerable()
        .FirstOrDefault(row => row.Field<string>("item") == "Cake");


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Saturday, October 26, 2019 11:52 PM
    Moderator
  • Sorry, my question doesn't seem well organized.

    I cannot set the value of column B like 'IceCream' manually. The values are various.

    I want to find which Menu are ordered by only 1 customer. And furthermore,  2 customers for the next time.


    • Edited by Kay_Lee Sunday, October 27, 2019 1:00 AM
    Sunday, October 27, 2019 1:00 AM
  • I can understand about not setting values manually but to demonstrate I need to have mocked data so you can see how code flows. So if you want all items for a customer rather than searching by food you search by customer id.

    A refactor

    public class MenuSelection
    {
        public int CustomerId { get; set; }
        public string Item { get; set; }
    }

    Example

    var customerIdentifier = 2;
    var customerOrders = new List<MenuSelection>()
    {
        new MenuSelection() {CustomerId = 1, Item = "Pizza"},
        new MenuSelection() {CustomerId = 2, Item = "Cake"},
        new MenuSelection() {CustomerId = 1, Item = "Ice Cream"},
        new MenuSelection() {CustomerId = 2, Item = "Burger"}
    };
    
    
    var customerOrder = customerOrders
        .Where(food => food.CustomerId == customerIdentifier)
        .ToList();
    
    if (customerOrder.Count > 0)
    {
        foreach (var selection in customerOrder)
        {
            Console.WriteLine(selection.Item);
        }
    }

     

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Sunday, October 27, 2019 1:36 AM
    Moderator
  • Sorry, Karen, 

    Again, I want to find all data(rows) Menu are ordered by only 1 customer.

    Would you kindly just let me get the row with customerID 4, IceCream without indicating specific Menu(automatically) ?

    Sunday, October 27, 2019 4:06 AM
  • Check this example too:

    class Item
    {
    	public int customerID;
    	public string Menu;
    }
    
    . . .
    
    var items = new List<Item>
    {
    	new Item{ customerID = 777  , Menu = "Pizza" },
    	new Item{ customerID = 929  , Menu = "Pizza" },
    	new Item{ customerID = 4    , Menu = "IceCream" },
    	new Item{ customerID = 224  , Menu = "Cake" },
    	new Item{ customerID = 429  , Menu = "Strawberry" },
    	new Item{ customerID = 1119 , Menu = "Strawberry" },
    	new Item{ customerID = 7979 , Menu = "Pizza" },
    	new Item{ customerID = 99   , Menu = "Cake" },
    	new Item{ customerID = 100  , Menu = "Strawberry" },
    };
    
    int N = 1; // how many occurences
    
    var names = 
    	items
    		.GroupBy( i => i.Menu )
    		.Where( g => g.Count( ) == N )
    		.Select( g => g.Key )
    		.ToList();
    
    var selected_items = 
    	items
    		.Where( i => names.Contains( i.Menu ) )
    		.OrderBy( i => i.Menu )
    		.ToList( );


    Sunday, October 27, 2019 9:35 AM
  • Many thanks Viorel.

    Your idea makes more sense.

    I apologize there was missing point in my question that the customerID 4 places IceCream Orders several times in a year. This means there're several set of customerID 4, IceCream in a DataTable and we need to remove all the duplicates or do some work with LINQ because of these duplicates.

    I highly appreciate your excellent insight !


    • Edited by Kay_Lee Sunday, October 27, 2019 12:00 PM
    Sunday, October 27, 2019 11:59 AM