logo
Tags down

shadow

How do I most elegantly express left join with aggregate SQL as LINQ query


By : William Burke
Date : March 29 2020, 07:55 AM
it fixes the issue A solution, albeit one that defers handling of the null value to the code, could be:
code :
var collection=
    from u in db.Universe
    select new
    {
        u.id,
        u.name,
        MaxDate =(DateTime?)
       (
           from h in db.History
           where u.Id == h.Id
           && h.dateCol < yesterday
           select h.dateCol 
       ).Max()
    };


Share : facebook icon twitter icon

LINQ aggregate left join on SQL CE


By : Mamta
Date : March 29 2020, 07:55 AM
this one helps. Have you tried a query expression with a group by, closer to your T-SQL version?
code :
var invoices =
    from I in Invoices
    where I.Date >= start && I.Date <= end
    join P in Payments on I.InvoiceID equals P.InvoiceID into J
    group J.Sum(p => p.Amount) by new { I.InvoiceID, I.CustomerID, I.Amount, I.Date } into G
    let AmountPaid = G.Sum()
    let AmountDue = G.Key.Amount - AmountPaid
    orderby AmountDue descending
    select new
    {
        G.Key.InvoiceID,
        G.Key.CustomerID,
        AmountInvoiced = G.Key.Amount,
        InvoiceDate = G.Key.Date,
        AmountPaid,
        AmountDue
    };
var Invoices = new[] {
    new { InvoiceID = 1, CustomerID = 2, Amount = 2.5m, Date = DateTime.Today },
    new { InvoiceID = 2, CustomerID = 3, Amount = 5.5m, Date = DateTime.Today }
}.AsQueryable();
var Payments = new[] {
    new { InvoiceID = 1, Amount = 1m }
}.AsQueryable();
{ InvoiceID = 2, CustomerID = 3, AmountInvoiced = 5.5, InvoiceDate = 8/15/2009,
  AmountPaid = 0, AmountDue = 5.5 }
{ InvoiceID = 1, CustomerID = 2, AmountInvoiced = 2.5, InvoiceDate = 8/15/2009, 
  AmountPaid = 1, AmountDue = 1.5 }
var invoices =
    from I in Invoices
    where I.Date >= start && I.Date <= end
    join P in Payments on I.InvoiceID equals P.InvoiceID into J
    from PJ in J.DefaultIfEmpty() // Left Join
    group PJ by new { I.InvoiceID, I.CustomerID, I.Amount, I.Date } into G
    let AmountPaid = G.Sum(p => p == null ? 0 : p.Amount)
    // etc...

Linq to SQL: Left Join to MAX aggregate


By : Alex Loizou
Date : March 29 2020, 07:55 AM
With these it helps You could write this particular query in LINQ as follows--although this will result in a correlated subquery:
code :
var query = 
    from c in ctx.Customer
    select new
    {
        Customer = c,
        LatestInvoice = ctx.Invoice
            .Where(i => i.CustomerId == c.CustomerId)
            .OrderByDescending(i => i.InvoiceId)
            .FirstOrDefault();
    };
var latestInvoicesPerCustomerQuery = 
    from inv in ctx.Invoice
    group inv by inv.CustomerId into g
    select new { CustomerId = g.Key, InvoiceId = g.Max(inv => inv.InvoiceId) };

var customersAndLatestInvoicesQuery = 
    from customer in ctx.Customer
    join linv in latestInvoicesPerCustomer 
         on customer.CustomerId equals linv.CustomerId
         into latestInvoiceJoin
    from latestInvoice in latestInvoiceJoin.DefaultIfEmpty() // left join
    join invoice in ctx.Invoice 
         on latestInvoice.InvoiceId equals invoice.InvoiceId
    select new
    {
        Customer = customer,
        LatestInvoice = invoice
    };
var customersAndLatestInvoicesQuery = 
    from customer in ctx.Customer
    join linv in (
            from inv in ctx.Invoice
            group inv by inv.CustomerId into g
            select new 
            { 
                CustomerId = g.Key, 
                InvoiceId = g.Max(inv => inv.InvoiceId) 
            }
        ) 
        on customer.CustomerId equals linv.CustomerId
        into latestInvoiceJoin
    from latestInvoice in latestInvoiceJoin.DefaultIfEmpty() // left join
    join invoice in ctx.Invoice 
         on latestInvoice.InvoiceId equals invoice.InvoiceId
    select new
    {
        Customer = customer,
        LatestInvoice = invoice
    };

LINQ Left join on my than one field? I have the SQL working but can't convert it to a linq query


By : Galal Ramzy Salama
Date : March 29 2020, 07:55 AM
Any of those help I wonder if anyone can help , I think this is the direction you need to head down:
code :
from i in Item 
   join c in codes on new { i.itemid, i.catid } equals new { c.itemid, c.catid } 
   into grouped
   from g in grouped.DefaultIfEmpty()
   select new
   {
      ItemId = c.itemid,
      CatItemId = g != null ? g.itemid : "Empty"
   };

SQL Query with Aggregate function on Left Join of One-to-Many Relationship


By : DavidMiller
Date : March 29 2020, 07:55 AM
should help you out Assuming one user doesn't have two tasks with the same max difficulty, you can do something like this. Although, this isn't very performant. It will work fine on a small dataset, but should be redesigned if your dataset is very large at all. Hopefully it will get you pointed in the right direction.
code :
declare @users table (user_id int, username varchar(10))
declare @tasks table (task_id int, user_id int, taskname varchar(24), difficulty int)

insert into @users values
(1, 'John'),
(2, 'Sally'),
(3, 'Sam')

insert into @tasks values
(1, 1, 'prepare grocery list', 1),
(2, 1, 'do shopping', 2),
(3, 1, 'cook food', 3),
(4, 2, 'do shopping', 2),
(5, 2, 'prepare grocery list', 1),
(6, 3, 'cook food', 3)

select u.user_id, u.username, t.task_id, t.taskname, t.difficulty
from   @users u
       left join @tasks t on u.user_id = t.user_id
where  t.difficulty = (
         select max(x.difficulty)
         from   @tasks x
         where  t.user_id = x.user_id
       )
select u.user_id, u.username, t.task_id, t.taskname, t.difficulty
from   @users u
       left join @tasks t on u.user_id = t.user_id
       inner join (
         select x.user_id, max(x.difficulty) as max_difficulty
         from   @tasks x
         group by x.user_id
       ) as y on t.user_id = y.user_id and t.difficulty = y.max_difficulty
user_id     username   task_id     taskname                 difficulty
----------- ---------- ----------- ------------------------ -----------
1           John       3           cook food                3
2           Sally      4           do shopping              2
3           Sam        6           cook food                3

How to create Linq to Sql with left join, aggregate, and where clause


By : lordlewo
Date : March 29 2020, 07:55 AM
around this issue Does anyone know how to convert this query to LINQ to SQL? , You want something like this:
Related Posts Related Posts :
  • i got CS0029 C# Cannot implicitly convert type 'int' to 'bool'
  • Unable to find department name from IUserPeopleCollectionPage type Dataa MS Graph API
  • How to process data from a file in parallel in several threads and write them into another file while keeping the origin
  • How to check which animation is playing
  • Entity Framework Core generating -ve integer values for Primary Key column on a table row add
  • What is the best way to find out if a string is a roman number?
  • C# BinaryWriter not writing to file
  • Multiply relationships ef core
  • How to use generic Queue where the type also has a generic paramater?
  • POST with API in C#
  • How do I inherit from a base class and add another property?
  • Rename refactoring in VS Code
  • What is Difference Between Console.WriteLine($"welcome{name}") and Console.WriteLine("welcome {0}",n
  • Useless assignment - Sonar warning
  • How to approach microfrontends in ASP.NET?
  • How to implement dynamic values for *case* in c#?
  • Visual Studio 2019 on Linux
  • What can I use instead of a (Switch) here?
  • What is the use of private constructor in abstract class in c#?
  • Why my gameObjects are not dragged and snap?
  • Deserialize JSON to List
  • When a ".Net Standard" project is built - what does that mean?
  • Performance difference between Linq Select and ToList
  • Cannot Convert From Double To String c#
  • RS-232 erratic behavior - Follow up
  • Multiple Textbox Validations
  • Access properties of a virtual class in partial class
  • How to post a message to google chat room using C#? (**Error**: Request had insufficient authentication scopes)
  • How to get all USA timezone IDs using nodatime
  • How to pass object of unknown type to function
  • Is it OK to inherit an empty Interface?
  • If I implement IEquatable<T>, will I lose the option to compare by reference?
  • Authorize with both ASP.NET core MVC/Razor site AND a WebAPI
  • If Condition Simplification
  • ASP Net Core Web API: Client side GroupBy is not supported
  • C# I have a DLL file and I need to make a class that inherits from a class that's in the DLL file?
  • How can I cause a derived method to be called during an inherited base constructor?
  • How to subtract between two integer List in C#?
  • Using SerialPort variable through MainWindow
  • The most efficient way (performance/time wise) to find a highest value in a List with continuously changing values?
  • How to Deserialize Json string to Type without creating class
  • How to enforce this one-to-many constraint in C#?
  • MVC: Unable to update one of my model's properties
  • Task.WhenAll with LINQ select
  • I am creating the controller in asp dot net core but Giving Error
  • How to define a constraint in Ortools to set a limit of distinct values
  • How to check for Carriage Return of each element end in string array and add if not exists?
  • Multithreading | Thread was being aborted
  • Why would you use TransactionScope for Read-Only Database Calls in NET C#?
  • How to branch code based on class data type
  • Is there a way to linking two data values, simpler than Dictionary?
  • How to display a file in a folder without showing physical path?
  • Are binary tree nodes passed by reference?
  • Convert float to decimal, wrong value
  • Can someone tell me why (return DayName;) causes an ERROR cause it's an unassigned local variable?
  • Prohibit access to the private variable
  • c# How do I correctly compile code for distribution?
  • C# Passing a reference to a generic method to another method
  • Pass property name as parameter
  • Why converting empty value to datetime2 is throwing conversion error in SQL?
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org