Join_DB_Table - lucyberryhub/WPF.Tutorial GitHub Wiki

πŸ“ Sweet Berry-Cherry Join Tutorial πŸ’

Hey, cutie! πŸ“βœ¨ Today’s tutorial is all about joining tables in EF Core, but we’re doing it berry-cherry style! πŸ₯§ Imagine combining a basket of juicy berries with a bowl of cherries to make the most delicious dessert ever! Let’s whip up some sweet code magic. πŸ’πŸ’•

🌸 Overview of the Tutorial

We’re going to learn how to:

  1. Join two tables dynamically using EF Core. πŸ“πŸ’
  2. Select custom properties from the joined data (like picking the ripest fruit). 🌸
  3. Filter the data with a condition that fits your needs! 🌼
  4. Save the results into a JSON file for later berry-good use. 🍭

🌟 Berrylicious Concepts

Let’s break down this cute recipe!

πŸ“ Berry Table (Table 1)

The berry table contains sweet little berries with properties like:

  • BerryId: A unique ID for each berry.
  • BerryFlavor: The flavor profile of the berry.
  • BerryName: The adorable name of the berry!

πŸ’ Cherry Table (Table 2)

The cherry table contains the cherry toppings, with properties like:

  • CherryId: A unique ID for each cherry.
  • CherryType: The type of cherry.
  • CherryName: The cute name of the cherry!

πŸ“ Step-by-Step Tutorial Code

Let’s get our aprons on and bake this berry-cherry code pie! πŸ₯§βœ¨

Models

// πŸ“ BerryModel (Table 1)
public class BerryModel
{
    public string BerryId { get; set; }       // Unique berry identifier
    public string BerryFlavor { get; set; }  // Flavor of the berry
    public string BerryName { get; set; }    // Name of the berry
}

// πŸ’ CherryModel (Table 2)
public class CherryModel
{
    public string CherryId { get; set; }     // Unique cherry identifier
    public string CherryType { get; set; }   // Type of cherry
    public string CherryName { get; set; }   // Name of the cherry
}

DbContext

// 🌸 BerryCherryDbContext
public class BerryCherryDbContext : DbContext
{
    public DbSet<BerryModel> Berries { get; set; }    // πŸ“ Table of berries
    public DbSet<CherryModel> Cherries { get; set; }  // πŸ’ Table of cherries

    // Method to dynamically fetch a DbSet
    public DbSet<T> GetBerryCherrySet<T>() where T : class
    {
        return Set<T>();
    }
}

Join the Berry-Cherry Data

using (var berryCherryContext = new BerryCherryDbContext())
{
    // πŸ“ Fetch the Berry DbSet
    var berrySet = berryCherryContext.GetBerryCherrySet<BerryModel>();
    
    // πŸ’ Fetch the Cherry DbSet
    var cherrySet = berryCherryContext.GetBerryCherrySet<CherryModel>();

    // πŸ₯§ Join the Berry-Cherry Tables
    var berryCherryPie = await berrySet
        .Join(cherrySet,
              berry => berry.BerryId,           // Match on BerryId
              cherry => cherry.CherryId,       // Match on CherryId
              (berry, cherry) => new           // Combine the results
              {
                  BerryId = berry.BerryId,
                  BerryFlavor = berry.BerryFlavor,
                  BerryName = berry.BerryName,
                  CherryId = cherry.CherryId,
                  CherryType = cherry.CherryType,
                  CherryName = cherry.CherryName
              })
        .Where(pie => pie.BerryFlavor == "Sweet")   // Filter for sweet berries 🍬
        .ToListAsync();

    // 🍭 Save to a JSON File
    if (berryCherryPie.Any())
    {
        await BerryJsonHelper.SaveBerryDataAsync("berry-cherry-pie.json", berryCherryPie);
    }
    else
    {
        MessageBox.Show("No berries or cherries found! πŸ“πŸ’", 
                        "Oh No!", 
                        MessageBoxButton.OK, 
                        MessageBoxImage.Information);
    }
}

Berry-Cherry JSON Helper

Here’s the helper class to save your data in a super cute JSON file! πŸ’Ύβœ¨

public static class BerryJsonHelper
{
    public static async Task SaveBerryDataAsync<T>(string filePath, T data)
    {
        var json = JsonConvert.SerializeObject(data, Formatting.Indented);  // Pretty JSON 🌟
        await File.WriteAllTextAsync(filePath, json);
        MessageBox.Show("Your berry-cherry pie has been saved! πŸ₯§", 
                        "Yay!", 
                        MessageBoxButton.OK, 
                        MessageBoxImage.Information);
    }
}

πŸ₯° Berry-Cherry Breakdown

πŸ“ What We Did

  1. Joined the Tables:

    • The berrySet (Berries πŸ“) and cherrySet (Cherries πŸ’) were joined using their BerryId and CherryId.

  2. Selected Sweet Properties:

    • We picked properties like BerryName and CherryType for the result.

  3. Filtered the Results:

    • We applied a condition to only select berries with a BerryFlavor of "Sweet".

  4. Saved to JSON:

    • We saved the yummy result to a JSON file named berry-cherry-pie.json for later use.

πŸ’ Sample Output JSON

Here’s what your berry-cherry-pie.json might look like:

[
  {
    "BerryId": "B001",
    "BerryFlavor": "Sweet",
    "BerryName": "Strawberry",
    "CherryId": "C001",
    "CherryType": "Red",
    "CherryName": "Red Cherry"
  },
  {
    "BerryId": "B002",
    "BerryFlavor": "Sweet",
    "BerryName": "Blueberry",
    "CherryId": "C002",
    "CherryType": "Black",
    "CherryName": "Black Cherry"
  }
]

🌸 Final Notes

Wasn’t that just the cutest way to join tables? πŸ“πŸ’ Remember, coding doesn’t have to be boringβ€”add a touch of sweetness, and it’ll be berry fun! 🌼

Oh no, I got too serious for a moment! Let’s make this adorable and full of Lucy Berry’s sparkling charm! πŸŒΈπŸ“βœ¨ Rewritten in cutie, girly Lucy Berry style, coming right up!

πŸ’βœ¨ LoadData Adventure 🌟

Hi, sweeties! πŸ’– It’s time to sprinkle some magic into your database journey with the ultimate LoadData method! 🍭 This method is perfect for fetching, joining, and saving data like a pro! Get ready to level up your app with lots of berry-flavored charm! πŸ“πŸ’

πŸ“ What Does This Magical Method Do? 🌟

  1. Fetch a special ingredient: Grabs a specific value from your table where the ID is a juicy "2." 🍬
  2. Berry-magic join: Combines two magical tables to create a sweet and delicious data result! πŸ“βœ¨
  3. Save the cherries on top: Saves everything to a JSON file, ready to display in your app. πŸ’ΎπŸ’
  4. Berry-cute error handling: Even if something goes wrong, Lucy Berry’s got your back! πŸ’–

🌸 Full Tutorial Code

Here’s the cutie-pie code for the magical LoadData method! Add it to your project and watch the magic happen! πŸ’–

private async Task LoadData()
{
    try
    {
        using (var berryCherryContext = new BerryCherryDbContext()) // 🌸 Open a magical Berry Cherry database session πŸ’
        {
            // 🍭 Sweet Table #1 (Berry Pie)
            var berryTable = berryCherryContext.GetBerrySet<EnvBerryPieModel>();

            // πŸ’ Sweet Table #2 (Cherry Tart)
            var cherryTable = berryCherryContext.GetBerrySet<EnvCherryTartModel>();

            // ✨ Fetch the special berry (Id = 2)! 🌸
            var cherryTopping = await berryTable
                .Where(b => EF.Property<string>(b, "BerryId") == "2") // 🌟 Select the juicy ID 2 πŸ’
                .Select(b => b.BerrySweetnessText)
                .FirstOrDefaultAsync();

            // πŸ“βœ¨ Perform a berry-cherry join! πŸ’
            var magicalData = await berryTable
                .Join(cherryTable,
                      berry => EF.Property<string>(berry, "BerryId"), // πŸ₯§ Match BerryId πŸ“
                      cherry => EF.Property<string>(cherry, "CherryId"), // πŸ’ Match CherryId πŸ’
                      (berry, cherry) => new  // 🍭 Result Selector ✨
                      {
                          BerryDim1 = berry.BerrySmallText,
                          BerryDim2 = berry.BerryLargeText,
                          CherryTartExtra = cherry.CherrySpecialText,
                          BerryTartSecret = cherryTopping, // πŸ“ From the previous fetch!
                          CherryDim1 = cherry.CherrySmallText,
                          CherryDim2 = cherry.CherryLargeText,
                      })
                //.Where(item => item.BerryDim1 == "Extra Sweet") // Optional: Filter for the sweetest berries 🍭
                .ToListAsync();

            // πŸ’Ύ Save your magical data to a JSON file! ✨
            if (magicalData.Any())
            {
                await JsonHelper.WriteToJsonFileAsync(viewModel.BerryCherryJsonFile, magicalData); // Save it! πŸ’
                await viewModel.ReloadBerryCherryData(); // Reload the sweet data 🌸✨
            }
            else
            {
                MessageBox.Show("Oh no! No sweet data found. 🍭 Try again later!", 
                                "Oopsie Daisy! πŸ“", 
                                MessageBoxButton.OK, 
                                MessageBoxImage.Information);
            }
        }
    }
    catch (Exception berryOops)
    {
        MessageBox.Show($"Oopsie, something went wrong! 🌸 Error: {berryOops.Message}", 
                        "Uh-oh! πŸ’", 
                        MessageBoxButton.OK, 
                        MessageBoxImage.Error);
    }
}

πŸ’ Step-by-Step Cutie Guide! πŸ’–

1️⃣ Open Your Berry-Cherry Database πŸ“

We use BerryCherryDbContext to connect to our magical berry database! It’s super easy to fetch data from your tables with this setup.

2️⃣ Fetch Your Special Ingredient ✨

Look for the berry with Id = 2 (because every sweet recipe needs a hero!). This is done with:

var cherryTopping = await berryTable
    .Where(b => EF.Property<string>(b, "BerryId") == "2")
    .Select(b => b.BerrySweetnessText)
    .FirstOrDefaultAsync();

3️⃣ Perform the Berry-Cherry Join πŸ“πŸ’

Join EnvBerryPieModel (berry table) and EnvCherryTartModel (cherry table) using their magical IDs. Combine their properties into a berry-cherry result:

var magicalData = await berryTable
    .Join(cherryTable,
          berry => EF.Property<string>(berry, "BerryId"), 
          cherry => EF.Property<string>(cherry, "CherryId"), 
          (berry, cherry) => new  
          {
              // Combine berry and cherry data 🍭
              BerryDim1 = berry.BerrySmallText,
              BerryDim2 = berry.BerryLargeText,
              CherryTartExtra = cherry.CherrySpecialText,
              BerryTartSecret = cherryTopping, 
              CherryDim1 = cherry.CherrySmallText,
              CherryDim2 = cherry.CherryLargeText,
          })
    .ToListAsync();

4️⃣ Save Your Magical Results πŸ’Ύ

Write the combined berry-cherry data to a JSON file. Sweet and simple!

5️⃣ Handle Oopsie Moments! πŸ“πŸ’

If something goes wrong, display a berry-cute error message to keep things friendly and fun! 🌸

πŸŽ‰ Let’s See an Example! πŸ’

Here’s how you can call this adorable method in your app:

// Load all the berry-cherry magic βœ¨πŸ“
await LoadData(); 
MessageBox.Show("Yay! The sweet data is ready! πŸ’", "Berry Success! 🌸", 
                MessageBoxButton.OK, MessageBoxImage.Information);
⚠️ **GitHub.com Fallback** ⚠️