Visual Basic

Public Sub Show_SubjectSummary()
        sqlQRY = "SELECT * FROM tblstudetail where" _
             & "[session] = '2015' AND" _
             & "[course] = 'B.A. I' AND" _
            & "[ADstatus] LIKE 'OK'"
        ds = New DataSet
        da = New SqlDataAdapter(sqlQRY, Conn)
        da.Fill(ds, "tblstudetail")
        RDLCreportview.ReportViewer1.LocalReport.ReportEmbeddedResource = Application.StartupPath & "\Report\SubjectSummary.rdlc"
        sReportDataSource.Name = "DataSet1"
        sReportDataSource.Value = ds.Tables(0)
        RDLCreportview.ReportViewer1.LocalReport.ReportPath = Application.StartupPath & "\Report\SubjectSummary.rdlc"
        RDLCreportview.ReportViewer1.SetDisplayMode(DisplayMode.PrintLayout)
        RDLCreportview.ReportViewer1.RefreshReport()
        ds.Dispose()
        ds = Nothing
        RDLCreportview.ShowDialog()
        RDLCreportview.Dispose()
        Conn.Close()
    End Sub

da = new SqlDataAdapter("SELECT * FROM subsummary WHERE [course] = @Course", Conn)
da.SelectCommand.Parameters.AddWithValue("@Course", ddlCourse.SelectedValue)

ds = New DataSet()
da.Fill(ds, "subsummary")

sReportDataSource.Name = "DataSet1"
sReportDataSource.Value = ds.Tables(0)

RDLCreportview.ReportViewer1.LocalReport.ReportPath = Application.StartupPath & "\Report\SubjectSummary.rdlc"
RDLCreportview.ReportViewer1.SetDisplayMode(DisplayMode.PrintLayout)
RDLCreportview.ReportViewer1.RefreshReport()

ds.Dispose()
ds = Nothing

RDLCreportview.ShowDialog()
RDLCreportview.Dispose()

Public Sub Show_SubjectSummary()
        da = New SqlDataAdapter("SELECT * FROM viewsubsummary WHERE [course] = 'B.A. I'", Conn)
        'da.SelectCommand.Parameters.AddWithValue("@Course", cmbsession.SelectedValue)
        ds = New DataSet()
        da.Fill(ds, "viewsubsummary")
        sReportDataSource.Name = "DataSet1"
        sReportDataSource.Value = ds.Tables(0)
        RDLCreportview.ReportViewer1.LocalReport.ReportPath = Application.StartupPath & "\Report\SubjectSummary.rdlc"
        RDLCreportview.ReportViewer1.SetDisplayMode(DisplayMode.PrintLayout)
        RDLCreportview.ReportViewer1.RefreshReport()
        ds.Dispose()
        ds = Nothing
        RDLCreportview.ShowDialog()
        RDLCreportview.Dispose()
    End Sub

RDLCreportview.ReportViewer1.LocalReport.DataSources.Clear()
RDLCreportview.ReportViewer1.LocalReport.DataSources.Add(New ReportDataSource("DataSet1", ds.Tables(0)))

SELECT * FROM viewsubsummary WHERE [course] = 'B.A. I'

SELECT
    T.Course,
    S.subject,
    SUM(CASE T.Adcat WHEN 'Reg' THEN 1 ELSE 0 END) As Reg,
    SUM(CASE T.Adcat WHEN 'Pri' THEN 1 ELSE 0 END) As Pri
FROM
    YourTable As T
    CROSS APPLY
    (
        VALUES (sub1), (sub2), (sub3), (sub4), (sub5)
    ) As S (subject)
WHERE
    S.subject Is Not Null
And
    S.subject != ''
GROUP BY
    T.Course,
    S.subject
ORDER BY
    T.Course,
    S.subject
;

Course        subject        Reg                          Pri          
 
      BA1            HL           1                              1
 
                     EL           1                              0
 
                     SL           1                              0
 
                     Hlit         2                              1
 
                     His          1                              2
 
                     PS           1                              1
 
                     HS           1                              0
 
                     Socio        2                              0
	Count adcat	  2		                 1

DHARM PAL wrote:

how can i add statement to also count number of students in every category..

DHARM PAL wrote:

is there a way to add where clause based on column ADstatus and OLstatus AND EXstatus programetically

var result = db.Contacts
.IncludeEFU(db, x => x.PhoneNumbers
    .Where(n => n.Number == "10134")
    .OrderBy(p => p.ContactId)
    .ThenByDescending(p => p.Number))
.ToList();

Dim result = db.Contacts _
.IncludeEFU(db, Function(x) x.PhoneNumbers _
.Where(Function(n) n.Number = "10134") _
.OrderBy(Function(p) p.ContactId) _
.ThenByDescending(Function(p) p.Number)).ToList()

An unhandled exception of type 'System.ArgumentException' occurred in EntityFramework.Utilities.dll

Additional information: Could not find a MemberExpression

https://github.com/MikaelEliasson/EntityFramework.Utilities/issues/29[^]

Hello !
I'm using VB.net and entity framework 6.1
I try to use the IncludeEFU.
This is the Code :

VB.NET

Dim MyList as IeNumerable(Of Article)
MyList= context.Articles.IncludeEFU(context, Function(t2) t2.F1_item).ToList

But I'm getting this error :
An unhandled exception of type 'System.ArgumentException' occurred in EntityFramework.Utilities.dll

Additional information: Could not find a MemberExpression

( note that the standart .Include() command works correctly ).

What can I do ?
Thank you !

Quote:

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.Core.Metadata.Edm;
using System.Data.Entity.Core.Objects;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;

namespace EntityFramework.Utilities
{
public static class EFQueryHelpers
{
///

/// Loads a child collection in a more efficent way than the standard Include. Will run all involved queries as NoTracking
///

/// <typeparam name="T">
/// <typeparam name="TChild">
/// <param name="query" />
/// <param name="context" />
/// <param name="collectionSelector" />The navigation property. It can be filtered and sorted with the methods Where,OrderBy(Descending),ThenBy(Descending)
/// <returns>
public static EFUQueryable<t> IncludeEFU<t, tchild="">(this IQueryable<t> query, DbContext context, Expression<func<t, ienumerable<tchild="">>> collectionSelector)
where T : class
where TChild : class
{
var octx = (context as IObjectContextAdapter).ObjectContext;
var cSpaceTables = octx.MetadataWorkspace.GetItems<entitytype>(DataSpace.CSpace);
var cSpaceType = cSpaceTables.Single(t => t.Name == typeof(T).Name); //Use single to avoid any problems with multiple tables using the same type
var keys = cSpaceType.KeyProperties;
if (keys.Count > 1)
{
throw new InvalidOperationException("The include method only works on single key entities");
}

var fkGetter = GetForeignKeyGetter<t, tchild="">(cSpaceTables);

PropertyInfo pkInfo = typeof(T).GetProperty(keys.First().Name);
var pkGetter = MakeGetterDelegate<t>(pkInfo);

var childCollectionModifiers = new List<methodcallexpression>();
var childProp = SetCollectionModifiersAndGetChildProperty<t, tchild="">(collectionSelector, childCollectionModifiers);
var setter = MakeSetterDelegate<t>(childProp);

var e = new IncludeExecuter<t>
{
ElementType = typeof(TChild),
SingleItemLoader = (parent) =>
{
if (parent == null)
{
return;
}
var children = octx.CreateObjectSet<tchild>();
var lambdaExpression = GetRootEntityToChildCollectionSelector<t, tchild="">(cSpaceType);

var q = ApplyChildCollectionModifiers<tchild>(children, childCollectionModifiers);

var rootPK = pkGetter((T)parent);
var param = Expression.Parameter(typeof(TChild), "x");
var fk = GetFKProperty<t, tchild="">(cSpaceTables);
var body = Expression.Equal(Expression.Property(param, fk), Expression.Constant(rootPK));
var where = Expression.Lambda<func<tchild, bool="">>(body, param);

q = q.AsNoTracking().Where(where);

setter((T)parent, q.ToList());
},
Loader = (rootFilters, parents) =>
{
var baseType = typeof(T).BaseType != typeof(object) ? typeof(T).BaseType : typeof(T);

dynamic dynamicSet = octx.GetType()
.GetMethod("CreateObjectSet", new Type[] { })
.MakeGenericMethod(baseType)
.Invoke(octx, new Object[] { });

var set = dynamicSet.OfType<t>() as ObjectQuery<t>;

IQueryable<t> q = set;
foreach (var item in rootFilters)
{
var newSource = Expression.Constant(q);
var arguments = Enumerable.Repeat(newSource, 1).Concat(item.Arguments.Skip(1)).ToArray();
var newMethods = Expression.Call(item.Method, arguments);
q = q.Provider.CreateQuery<t>(newMethods);
}

var lambdaExpression = GetRootEntityToChildCollectionSelector<t, tchild="">(cSpaceType);

var childQ = q.SelectMany(lambdaExpression);
childQ = ApplyChildCollectionModifiers<tchild>(childQ, childCollectionModifiers);

var dict = childQ.AsNoTracking().ToLookup(fkGetter);
var list = parents.Cast<t>().ToList();

foreach (var parent in list)
{
var prop = pkGetter(parent);
var childs = dict.Contains(prop) ? dict[prop].ToList() : new List<tchild>();
setter(parent, childs);
}
}
};

return new EFUQueryable<t>(query.AsNoTracking()).Include(e);
}

private static IQueryable<tchild> ApplyChildCollectionModifiers<tchild>(IQueryable<tchild> childQ, List<methodcallexpression> childCollectionModifiers) where TChild : class
{
foreach (var item in childCollectionModifiers)
{
switch (item.Method.Name)
{
case "Where":
childQ = childQ.Where((Expression<func<tchild, bool="">>)item.Arguments[1]);
break;
case "OrderBy":
case "ThenBy":
case "OrderByDescending":
case "ThenByDescending":
childQ = SortQuery(childQ, item, item.Method.Name);
break;
default:
throw new NotSupportedException("The method " + item.Method.Name + " is not supported in the child query");
}
}
return childQ;
}

private static PropertyInfo SetCollectionModifiersAndGetChildProperty<t, tchild="">(Expression<func<t, ienumerable<tchild="">>> collectionSelector, List<methodcallexpression> childCollectionModifiers)
where T : class
where TChild : class
{
var temp = collectionSelector.Body;
while (temp is MethodCallExpression)
{
var mce = temp as MethodCallExpression;
childCollectionModifiers.Add(mce);
temp = mce.Arguments[0];
}
childCollectionModifiers.Reverse(); //We parse from right to left so reverse it
if (!(temp is MemberExpression))
{
throw new ArgumentException("Could not find a MemberExpression", "collectionSelector");
}

var childProp = (temp as MemberExpression).Member as PropertyInfo;
return childProp;
}

private static Func<tchild, object=""> GetForeignKeyGetter<t, tchild="">(System.Collections.ObjectModel.ReadOnlyCollection<entitytype> cSpaceTables)
where T : class
where TChild : class
{
var fkInfo = GetFKProperty<t, tchild="">(cSpaceTables);
var fkGetter = MakeGetterDelegate<tchild>(fkInfo);
return fkGetter;
}

private static PropertyInfo GetFKProperty<t, tchild="">(System.Collections.ObjectModel.ReadOnlyCollection<entitytype> cSpaceTables)
where T : class
where TChild : class
{
var cSpaceChildType = cSpaceTables.Single(t => t.Name == typeof(TChild).Name); //Use single to avoid any problems with multiple tables using the same type
var fk = cSpaceChildType.NavigationProperties.First(n => n.ToEndMember.GetEntityType().Name == typeof(T).Name).GetDependentProperties().First();
var fkInfo = typeof(TChild).GetProperty(fk.Name);
return fkInfo;
}

private static IQueryable<tchild> SortQuery<tchild>(IQueryable<tchild> query, MethodCallExpression item, string method)
{
var body = (item.Arguments[1] as LambdaExpression);

MethodCallExpression call = Expression.Call(
typeof(Queryable),
method,
new[] { typeof(TChild), body.Body.Type },
query.Expression,
Expression.Quote(body));

return (IOrderedQueryable<tchild>)query.Provider.CreateQuery<tchild>(call);
}

private static Expression<func<t, ienumerable<tchild="">>> GetRootEntityToChildCollectionSelector<t, tchild="">(EntityType cSpaceType)
where T : class
where TChild : class
{
var parameter = Expression.Parameter(typeof(T), "t");
var memberExpression = Expression.Property(parameter, cSpaceType.NavigationProperties.First(p => p.ToEndMember.GetEntityType().Name == typeof(TChild).Name).Name);
var lambdaExpression = Expression.Lambda<func<t, ienumerable<tchild="">>>(memberExpression, parameter);
return lambdaExpression;
}

static Action<t, object=""> MakeSetterDelegate<t>(PropertyInfo property)
{
MethodInfo setMethod = property.GetSetMethod();
if (setMethod != null && setMethod.GetParameters().Length == 1)
{
var target = Expression.Parameter(typeof(T));
var value = Expression.Parameter(typeof(object));
var body = Expression.Call(target, setMethod,
Expression.Convert(value, property.PropertyType));
return Expression.Lambda<action<t, object="">>(body, target, value)
.Compile();
}
else
{
return null;
}
}

static Func<x, object=""> MakeGetterDelegate<x>(PropertyInfo property)
{
MethodInfo getMethod = property.GetGetMethod();
if (getMethod != null)
{
var target = Expression.Parameter(typeof(X));
var body = Expression.Call(target, getMethod);
Expression conversion = Expression.Convert(body, typeof(object));
return Expression.Lambda<func<x, object="">>(conversion, target)
.Compile();
}
else
{
return null;
}
}
}
}

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq.Expressions;

namespace EntityFramework.Utilities
{
    public interface IIncludeContainer<T>
    {
        IEnumerable<IncludeExecuter<T>> Includes { get; }
    }

    public class IncludeExecuter<T>
    {
        internal Type ElementType { get; set; }
        internal Action<IEnumerable<MethodCallExpression>, IEnumerable> Loader { get; set; }
        internal Action<object> SingleItemLoader { get; set; }
    }
}