Visual Basic

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; }
    }
}

   Private List1 As New List(Of Article)
    Private Sub Window_Loaded(sender As Object, e As RoutedEventArgs)

        For i As Integer = 0 To 6
            Dim f As New Article
            f.ID = 2
            f.Td = i
            List1.Add(f)
        Next
        lst.ItemsSource = List1
    End Sub

    Private Sub Button_Click(sender As Object, e As RoutedEventArgs)
        SearchString = "id=2, td=3"
        Dim predicate As Predicate(Of Article) = AddressOf FindArticles

        lst.ItemsSource = List1.FindAll(predicate)
    End Sub

    Dim SearchString As String = ""

    Private Function FindArticles(obj As Article) As Boolean

        Dim ListOfProperties As New List(Of String)
        Dim ValueOfProperties As New List(Of String)

        If SearchString = "" Then
            Return True
        End If

        Dim NumberOfArguments() As String
        NumberOfArguments = SearchString.Split(",")
        For Each Str As String In NumberOfArguments
            Dim test() As String = Str.Trim.Split("=")
            ListOfProperties.Add(test(0).Trim)
            ValueOfProperties.Add(test(1).Trim)
        Next

        Dim ReturnValues As New List(Of Boolean)
        '   Dim TrueValue As Boolean = False

        For Each p As System.Reflection.PropertyInfo In obj.GetType().GetProperties()
            If p.CanRead And p.CanWrite Then

                For i As Integer = 0 To ListOfProperties.Count - 1
                    If p.Name.ToLower = ListOfProperties(i).ToLower Then

                        Dim t As Type = If(Nullable.GetUnderlyingType(p.PropertyType), p.PropertyType)
                        Dim safeValue As Object = If((ValueOfProperties(i) Is Nothing), Nothing, Convert.ChangeType(ValueOfProperties(i), t))

                        'Get the value
                        Dim f = p.GetValue(obj)

                        'This would set the value
                        '  p.SetValue(p, safeValue, Nothing)

                        'Is it the same type?
                        If safeValue IsNot Nothing Then
                            If f = safeValue Then
                                ReturnValues.Add(True)
                            Else
                                ReturnValues.Add(False)
                            End If
                        Else
                            'If you end up here you have entered the wrong element type of the property
                            ' like an integer instead of a point etc
                        End If
                    End If
                Next
            End If
        Next


        Dim truevalue As Boolean = False
        ' This is And logic on the result
        If ReturnValues.Count <> 0 Then
            Dim tempvalue As Boolean = True
            For Each Bools As Boolean In ReturnValues
                tempvalue = tempvalue And Bools
            Next
            truevalue = tempvalue
        End If

        Return TrueValue
    End Function
End Class

Class Article
    Private pId As Integer
    Public Property ID() As Integer
        Get
            Return pId
        End Get
        Set(ByVal value As Integer)
            pId = value
        End Set
    End Property

    Private pTd As Integer
    Public Property Td() As Integer
        Get
            Return pTd
        End Get
        Set(ByVal value As Integer)
            pTd = value
        End Set
    End Property

End Class

list1 = From t In context.Articles.Include("F2") Order By t.name Ascending
              Select t