locked
Linq query with join,groupby and where condition RRS feed

  • Question

  • public List<Expanse> Get(string user, DateTime date)
            {

                return from i in db.Expanse
                       join e in db.Categorys on
                       i.CategoryId equals e.Id into eGroup
                       from both in eGroup.DefaultIfEmpty()
                       where i.user.Equals(user) && i.Date.Equals(date)
                       select new
                       {

                           i.Date,
                           i.Amount,
                           both.Type,
                       };

    not working error explicit type cast error


    • Edited by aky73 Saturday, September 12, 2020 1:35 PM
    Saturday, September 12, 2020 9:54 AM

Answers

  • public  IQueryable<Object> GetExpanse(string user , DateTime date)

    var re = from c in categories join e in expanses on c.CatId equals e.CatId group new { c, e } by new { e.Date, e.CatId, c.Type, e.User } into g let eGroup = g.FirstOrDefault() let category = eGroup.c let expanse = eGroup.e where expanse.CatId == 0 && expanse.Date==Convert.ToDateTime("2020-02-17") select new { Date = expanse.Date, Amount = g.Sum(x =>x.e.Amount), Type = category.Type, User = expanse.User };

    return re.AsQueryable()

    On sql ,linqpad and even after debug it returns value but when return it from postman than it gives following error

    <Error>
    <Message>An error has occurred.</Message>
    <ExceptionMessage>The 'ObjectContent`1' type failed to serialize the response body for content type 'application/xml; charset=utf-8'.</ExceptionMessage>
    <ExceptionType>System.InvalidOperationException</ExceptionType>
    <StackTrace/>
    <InnerException>
    <Message>An error has occurred.</Message>
    <ExceptionMessage>Type '<>f__AnonymousType11`4[System.DateTime,System.Decimal,System.String,System.String]' cannot be serialized. Consider marking it with the DataContractAttribute attribute, and marking all of its members you want serialized with the DataMemberAttribute attribute. If the type is a collection, consider marking it with the CollectionDataContractAttribute. See the Microsoft .NET Framework documentation for other supported types.</ExceptionMessage>
    <ExceptionType>System.Runtime.Serialization.InvalidDataContractException</ExceptionType>
    <StackTrace> at System.Runtime.Serialization.DataContract.DataContractCriticalHelper.ThrowInvalidDataContractException(String message, Type type) at System.Runtime.Serialization.DataContract.DataContractCriticalHelper.CreateDataContract(Int32 id, RuntimeTypeHandle typeHandle, Type type) at System.Runtime.Serialization.DataContract.DataContractCriticalHelper.GetDataContractSkipValidation(Int32 id, RuntimeTypeHandle typeHandle, Type type) at System.Runtime.Serialization.XmlObjectSerializerContext.GetDataContract(RuntimeTypeHandle typeHandle, Type type) at System.Runtime.Serialization.XmlObjectSerializerWriteContext.SerializeWithXsiType(XmlWriterDelegator xmlWriter, Object obj, RuntimeTypeHandle objectTypeHandle, Type objectType, Int32 declaredTypeID, RuntimeTypeHandle declaredTypeHandle, Type declaredType) at System.Runtime.Serialization.XmlObjectSerializerWriteContext.InternalSerialize(XmlWriterDelegator xmlWriter, Object obj, Boolean isDeclaredType, Boolean writeXsiType, Int32 declaredTypeID, RuntimeTypeHandle declaredTypeHandle) at WriteArrayOfanyTypeToXml(XmlWriterDelegator , Object , XmlObjectSerializerWriteContext , CollectionDataContract ) at System.Runtime.Serialization.CollectionDataContract.WriteXmlValue(XmlWriterDelegator xmlWriter, Object obj, XmlObjectSerializerWriteContext context) at System.Runtime.Serialization.XmlObjectSerializerWriteContext.WriteDataContractValue(DataContract dataContract, XmlWriterDelegator xmlWriter, Object obj, RuntimeTypeHandle declaredTypeHandle) at System.Runtime.Serialization.XmlObjectSerializerWriteContext.SerializeWithoutXsiType(DataContract dataContract, XmlWriterDelegator xmlWriter, Object obj, RuntimeTypeHandle declaredTypeHandle) at System.Runtime.Serialization.DataContractSerializer.InternalWriteObjectContent(XmlWriterDelegator writer, Object graph, DataContractResolver dataContractResolver) at System.Runtime.Serialization.DataContractSerializer.InternalWriteObject(XmlWriterDelegator writer, Object graph, DataContractResolver dataContractResolver) at System.Runtime.Serialization.XmlObjectSerializer.WriteObjectHandleExceptions(XmlWriterDelegator writer, Object graph, DataContractResolver dataContractResolver) at System.Runtime.Serialization.DataContractSerializer.WriteObject(XmlWriter writer, Object graph) at System.Net.Http.Formatting.XmlMediaTypeFormatter.WriteToStream(Type type, Object value, Stream writeStream, HttpContent content) at System.Net.Http.Formatting.XmlMediaTypeFormatter.WriteToStreamAsync(Type type, Object value, Stream writeStream, HttpContent content, TransportContext transportContext, CancellationToken cancellationToken) --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.WebHost.HttpControllerHandler.<WriteBufferedResponseContentAsync>d__1b.MoveNext()</StackTrace>
    </InnerException>
    </Error>

     I Have solved by using single line of code 

                GlobalConfiguration.Configuration.Formatters.JsonFormatter.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore;

    Thanks Timon for helping 

    Thursday, September 17, 2020 1:33 PM
  • Hi,

    I'm glad to here that.

    Please click on the "Mark as answer" option of the reply that solved your question, so that it will help other members to find the solution quickly if they face a similar issue.

    Best Regards,

    Timon



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by aky73 Friday, September 18, 2020 8:55 AM
    Friday, September 18, 2020 6:21 AM

All replies

  • Assuming you don't know which part of the statement is doing this, best thing to do is to start with code up to and including the join, does that work? If yes add in the group without the where, does that work? 

    Otherwise simply reading the code I see nothing wrong.


    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, September 12, 2020 11:16 AM
  •            

     public IQueryable<Expanse> Get(string user, DateTime date)
            {

      return (from i in db.Expanse
                        join e in db.Category on
                        i.CategoryId equals e.Id into eGroup
                        from both in eGroup.DefaultIfEmpty()
                           
                        select new
                        {
                            date = i.Date,
                            amount = i.Amount,
                            type = both.Type,

                        }).AsQueryable();

    }

    when return it gives below error


    Severity Code Description Project File Line Suppression State
    Error CS0266 Cannot implicitly convert type 'System.Linq.IQueryable<<anonymous type: System.DateTime date, decimal amount, string type>>' to 'System.Linq.IQueryable<XpanseWebApi.Models.Expanse>'. An explicit conversion exists (are you missing a cast?) 


    • Edited by aky73 Saturday, September 12, 2020 1:38 PM
    Saturday, September 12, 2020 1:31 PM
  • Try ‘select new Expanse’ instead of ‘select new’, assuming that Expanse has the date, amount and type properties.

    Saturday, September 12, 2020 2:14 PM
  • but type is from category which is not showing after select new expanse and how to use group in same query 


    • Edited by aky73 Saturday, September 12, 2020 4:41 PM
    Saturday, September 12, 2020 4:02 PM
  • Hi aky73,

    Thank you for posting here.

    The type of the result obtained by linq is an anonymous type, which has a structure different from Expanse, so returning directly will definitely cause an error.

    As Viorel said, if Expanse has date, amount and type properties, something like this:

            public static List<Expanse> Get(string user, DateTime date)
            {
                return (from i in db.Expanse
                       join e in db.Categorys on
                       i.CategoryId equals e.Id into eGroup
                       from both in eGroup.DefaultIfEmpty()
                       where i.user.Equals(user) && i.Date.Equals(date)
                       select new Expanse
                       {
                          date = i.Date,
                          amount = i.Amount,
                          type = both.Type,
                       }).ToList();
            }

    But if Expanse does not contain the type property, you can modify the return type to object.

            public static object Get(string user, DateTime date)
            {
                return from i in db.Expanse
                       join e in db.Categorys on
                       i.CategoryId equals e.Id into eGroup
                       from both in eGroup.DefaultIfEmpty()
                       where i.user.Equals(user) && i.Date.Equals(date)
                       select new 
                       {
                           i.Date,
                           i.Amount,
                           both.Type,
                       };
            }

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, September 14, 2020 7:59 AM
  • Tnanks for reply 

    But second method is not returning value as json when i use it with webapi 

    basically  i have two table expanse and category and I wants to fetch record all from expanse and type from category i.e with where group and join condition 

    group by date and sum of amount



    • Edited by aky73 Monday, September 14, 2020 10:27 AM
    Monday, September 14, 2020 9:47 AM
  • Hi aky73,

    Could you please show us the structure of the two tables, preferably with some test data, and then tell us what the expected result is?

    If you need json, you can install Newtonsoft.Json, and then serialize the returned object into a json string.

       string jsonString =  JsonConvert.SerializeObject(object);

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, September 15, 2020 8:34 AM
  •  method 1

    public IQueryable<Expanse> Get(string user, DateTime date)
            {

      return (from i in db.Expanse
                        join e in db.Category on
                        i.CategoryId equals e.Id into eGroup
                        from both in eGroup.DefaultIfEmpty()
                           
                        select new
                        {
                            date = i.Date,
                            amount = i.Amount,
                            type = both.Type,

                        }).AsQueryable();

    }

    method 2

    return db.Expanse.Where(user =>
                                            user.User.Equals(user)
                                           && user.Date.Equals(date));

    my method 2 code works  fine but extended method 1 is what we are discussing i which i wants to return value through webapi controller and use with mobile client 

    with output date group 

    date

    type:xyz 

    type:abc

    then another date and so on.....

    table structure is as below

    Expanse table                                   

    id

    date

    user

    amount

    catid

    category table

    catid

    type

    Tuesday, September 15, 2020 9:21 AM
  • SELECT        Expanse.[Date], SUM(Expanse.Amount) AS amount, Category.Type, Expanse.User
    FROM            Expanse left outer JOIN
                             Category ON Expanse.Catid = Category.Id
    GROUP BY Expanse.[Date], Expanse.CatId, Category.Type, Expanse.user

    HAVING        (Expanse.user= '000000000') AND (Expanse.[Date] = '2020-02-17')

    2020-02-17  4000.00     aaa   000000000
    2020-02-17  6000.00         aaa 123654789
    2020-02-19    12000.00     bbb 258963147
    2020-08-04   1000.00        bbb 258963147
    2020-08-05  2000.00     aaa 000000000
    2020-08-06     100.00      ccc 111111111

    this is sql query want it to be in LINQ and my actual requirement is as above  using webapi controller with get method to return value.

    Tuesday, September 15, 2020 10:13 AM
  • Hi aky73,

    I created two new tables based on your description. Although there may be some differences from yours, the impact should not be significant.

    When I use sql to get the result:

    Then I used the following linq to get the results:

      var re = from c in categories
               join e in expanses on c.CatId equals e.CatId
               group new { c, e } by new { e.Date, e.CatId, c.Type, e.User } into g
    
               let eGroup = g.FirstOrDefault()
               let category = eGroup.c
               let expanse = eGroup.e
               where expanse.CatId == 0 && expanse.Date==Convert.ToDateTime("2020-02-17")
               select new
               {
                    Date = expanse.Date,
                    Amount = g.Sum(x =>x.e.Amount),
                    Type = category.Type,
                    User = expanse.User
                };
    

    Try it to see if it works for you.

    As for how to return, I think if a List<T> must be returned, is it possible to create a new class?

            class MyClass
            {
                public DateTime Date { get; set; }
                public Decimal Amount { get; set; }
                public String Type { get; set; }
                public String User { get; set; }
            }

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 17, 2020 3:21 AM
  • public  IQueryable<Object> GetExpanse(string user , DateTime date)

    var re = from c in categories join e in expanses on c.CatId equals e.CatId group new { c, e } by new { e.Date, e.CatId, c.Type, e.User } into g let eGroup = g.FirstOrDefault() let category = eGroup.c let expanse = eGroup.e where expanse.CatId == 0 && expanse.Date==Convert.ToDateTime("2020-02-17") select new { Date = expanse.Date, Amount = g.Sum(x =>x.e.Amount), Type = category.Type, User = expanse.User };

    return re.AsQueryable()

    On sql ,linqpad and even after debug it returns value but when return it from postman than it gives following error

    <Error>
    <Message>An error has occurred.</Message>
    <ExceptionMessage>The 'ObjectContent`1' type failed to serialize the response body for content type 'application/xml; charset=utf-8'.</ExceptionMessage>
    <ExceptionType>System.InvalidOperationException</ExceptionType>
    <StackTrace/>
    <InnerException>
    <Message>An error has occurred.</Message>
    <ExceptionMessage>Type '<>f__AnonymousType11`4[System.DateTime,System.Decimal,System.String,System.String]' cannot be serialized. Consider marking it with the DataContractAttribute attribute, and marking all of its members you want serialized with the DataMemberAttribute attribute. If the type is a collection, consider marking it with the CollectionDataContractAttribute. See the Microsoft .NET Framework documentation for other supported types.</ExceptionMessage>
    <ExceptionType>System.Runtime.Serialization.InvalidDataContractException</ExceptionType>
    <StackTrace> at System.Runtime.Serialization.DataContract.DataContractCriticalHelper.ThrowInvalidDataContractException(String message, Type type) at System.Runtime.Serialization.DataContract.DataContractCriticalHelper.CreateDataContract(Int32 id, RuntimeTypeHandle typeHandle, Type type) at System.Runtime.Serialization.DataContract.DataContractCriticalHelper.GetDataContractSkipValidation(Int32 id, RuntimeTypeHandle typeHandle, Type type) at System.Runtime.Serialization.XmlObjectSerializerContext.GetDataContract(RuntimeTypeHandle typeHandle, Type type) at System.Runtime.Serialization.XmlObjectSerializerWriteContext.SerializeWithXsiType(XmlWriterDelegator xmlWriter, Object obj, RuntimeTypeHandle objectTypeHandle, Type objectType, Int32 declaredTypeID, RuntimeTypeHandle declaredTypeHandle, Type declaredType) at System.Runtime.Serialization.XmlObjectSerializerWriteContext.InternalSerialize(XmlWriterDelegator xmlWriter, Object obj, Boolean isDeclaredType, Boolean writeXsiType, Int32 declaredTypeID, RuntimeTypeHandle declaredTypeHandle) at WriteArrayOfanyTypeToXml(XmlWriterDelegator , Object , XmlObjectSerializerWriteContext , CollectionDataContract ) at System.Runtime.Serialization.CollectionDataContract.WriteXmlValue(XmlWriterDelegator xmlWriter, Object obj, XmlObjectSerializerWriteContext context) at System.Runtime.Serialization.XmlObjectSerializerWriteContext.WriteDataContractValue(DataContract dataContract, XmlWriterDelegator xmlWriter, Object obj, RuntimeTypeHandle declaredTypeHandle) at System.Runtime.Serialization.XmlObjectSerializerWriteContext.SerializeWithoutXsiType(DataContract dataContract, XmlWriterDelegator xmlWriter, Object obj, RuntimeTypeHandle declaredTypeHandle) at System.Runtime.Serialization.DataContractSerializer.InternalWriteObjectContent(XmlWriterDelegator writer, Object graph, DataContractResolver dataContractResolver) at System.Runtime.Serialization.DataContractSerializer.InternalWriteObject(XmlWriterDelegator writer, Object graph, DataContractResolver dataContractResolver) at System.Runtime.Serialization.XmlObjectSerializer.WriteObjectHandleExceptions(XmlWriterDelegator writer, Object graph, DataContractResolver dataContractResolver) at System.Runtime.Serialization.DataContractSerializer.WriteObject(XmlWriter writer, Object graph) at System.Net.Http.Formatting.XmlMediaTypeFormatter.WriteToStream(Type type, Object value, Stream writeStream, HttpContent content) at System.Net.Http.Formatting.XmlMediaTypeFormatter.WriteToStreamAsync(Type type, Object value, Stream writeStream, HttpContent content, TransportContext transportContext, CancellationToken cancellationToken) --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.WebHost.HttpControllerHandler.<WriteBufferedResponseContentAsync>d__1b.MoveNext()</StackTrace>
    </InnerException>
    </Error>

    Thursday, September 17, 2020 9:11 AM
  • public  IQueryable<Object> GetExpanse(string user , DateTime date)

    var re = from c in categories join e in expanses on c.CatId equals e.CatId group new { c, e } by new { e.Date, e.CatId, c.Type, e.User } into g let eGroup = g.FirstOrDefault() let category = eGroup.c let expanse = eGroup.e where expanse.CatId == 0 && expanse.Date==Convert.ToDateTime("2020-02-17") select new { Date = expanse.Date, Amount = g.Sum(x =>x.e.Amount), Type = category.Type, User = expanse.User };

    return re.AsQueryable()

    On sql ,linqpad and even after debug it returns value but when return it from postman than it gives following error

    <Error>
    <Message>An error has occurred.</Message>
    <ExceptionMessage>The 'ObjectContent`1' type failed to serialize the response body for content type 'application/xml; charset=utf-8'.</ExceptionMessage>
    <ExceptionType>System.InvalidOperationException</ExceptionType>
    <StackTrace/>
    <InnerException>
    <Message>An error has occurred.</Message>
    <ExceptionMessage>Type '<>f__AnonymousType11`4[System.DateTime,System.Decimal,System.String,System.String]' cannot be serialized. Consider marking it with the DataContractAttribute attribute, and marking all of its members you want serialized with the DataMemberAttribute attribute. If the type is a collection, consider marking it with the CollectionDataContractAttribute. See the Microsoft .NET Framework documentation for other supported types.</ExceptionMessage>
    <ExceptionType>System.Runtime.Serialization.InvalidDataContractException</ExceptionType>
    <StackTrace> at System.Runtime.Serialization.DataContract.DataContractCriticalHelper.ThrowInvalidDataContractException(String message, Type type) at System.Runtime.Serialization.DataContract.DataContractCriticalHelper.CreateDataContract(Int32 id, RuntimeTypeHandle typeHandle, Type type) at System.Runtime.Serialization.DataContract.DataContractCriticalHelper.GetDataContractSkipValidation(Int32 id, RuntimeTypeHandle typeHandle, Type type) at System.Runtime.Serialization.XmlObjectSerializerContext.GetDataContract(RuntimeTypeHandle typeHandle, Type type) at System.Runtime.Serialization.XmlObjectSerializerWriteContext.SerializeWithXsiType(XmlWriterDelegator xmlWriter, Object obj, RuntimeTypeHandle objectTypeHandle, Type objectType, Int32 declaredTypeID, RuntimeTypeHandle declaredTypeHandle, Type declaredType) at System.Runtime.Serialization.XmlObjectSerializerWriteContext.InternalSerialize(XmlWriterDelegator xmlWriter, Object obj, Boolean isDeclaredType, Boolean writeXsiType, Int32 declaredTypeID, RuntimeTypeHandle declaredTypeHandle) at WriteArrayOfanyTypeToXml(XmlWriterDelegator , Object , XmlObjectSerializerWriteContext , CollectionDataContract ) at System.Runtime.Serialization.CollectionDataContract.WriteXmlValue(XmlWriterDelegator xmlWriter, Object obj, XmlObjectSerializerWriteContext context) at System.Runtime.Serialization.XmlObjectSerializerWriteContext.WriteDataContractValue(DataContract dataContract, XmlWriterDelegator xmlWriter, Object obj, RuntimeTypeHandle declaredTypeHandle) at System.Runtime.Serialization.XmlObjectSerializerWriteContext.SerializeWithoutXsiType(DataContract dataContract, XmlWriterDelegator xmlWriter, Object obj, RuntimeTypeHandle declaredTypeHandle) at System.Runtime.Serialization.DataContractSerializer.InternalWriteObjectContent(XmlWriterDelegator writer, Object graph, DataContractResolver dataContractResolver) at System.Runtime.Serialization.DataContractSerializer.InternalWriteObject(XmlWriterDelegator writer, Object graph, DataContractResolver dataContractResolver) at System.Runtime.Serialization.XmlObjectSerializer.WriteObjectHandleExceptions(XmlWriterDelegator writer, Object graph, DataContractResolver dataContractResolver) at System.Runtime.Serialization.DataContractSerializer.WriteObject(XmlWriter writer, Object graph) at System.Net.Http.Formatting.XmlMediaTypeFormatter.WriteToStream(Type type, Object value, Stream writeStream, HttpContent content) at System.Net.Http.Formatting.XmlMediaTypeFormatter.WriteToStreamAsync(Type type, Object value, Stream writeStream, HttpContent content, TransportContext transportContext, CancellationToken cancellationToken) --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.WebHost.HttpControllerHandler.<WriteBufferedResponseContentAsync>d__1b.MoveNext()</StackTrace>
    </InnerException>
    </Error>

     I Have solved by using single line of code 

                GlobalConfiguration.Configuration.Formatters.JsonFormatter.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore;

    Thanks Timon for helping 

    Thursday, September 17, 2020 1:33 PM
  • Hi,

    I'm glad to here that.

    Please click on the "Mark as answer" option of the reply that solved your question, so that it will help other members to find the solution quickly if they face a similar issue.

    Best Regards,

    Timon



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by aky73 Friday, September 18, 2020 8:55 AM
    Friday, September 18, 2020 6:21 AM
  • can we display record like this with above all stuff

    date: 2020-02-17 

             type1:10000

               type2:2000

    date: 2020-02-18

              type1:500

               type2:1500

    Sunday, September 20, 2020 6:21 PM
  • Hi aky73,

    Some records may have one type, and some may have two or more, so if you want to follow this format, the number of columns in each record is different, so I think it may not be able to display this way.

    Is the following format possible? Just need to modify the grouping conditions.

       var re = from c in categories
                join e in expanses on c.CatId equals e.CatId
                group new { c, e } by new { e.Date, c.Type } into g
    
                let eGroup = g.FirstOrDefault()
                let category = eGroup.c
                let expanse = eGroup.e
                orderby expanse.Date
                select new
                {
                     Date = expanse.Date,
                     Type = category.Type,
                     Amount = g.Sum(x =>x.e.Amount),
                 };

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, September 21, 2020 6:52 AM
  • This output is also coming with last query .Basically When I return json value and bind it to my mobile client using expandable collection app on that I want to show date on header and when click on header type with amount should be as per date like

    date: 2020-02-17 

             type1:10000

               type2:2000

    date: 2020-02-18

              type1:500

               type2:1500

    but when it display with control date is repeating but I want same date should be shown one time with different type 

    Saturday, September 26, 2020 12:24 PM