|
thanks man..
u made my day..
i was searching and trying this from one week.
its becoz i am not aware of view in sql server..
1 question if u have time??
how can i add statement to also count number of students in every category..
like this:
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
where my column adcat store reg, pri.
One more Question:
is there a way to add where clause based on column ADstatus and OLstatus AND EXstatus programetically because i have three combobox in my form which is having all status data like 'OK'/ 'NO'/'0'.
now based on status selected from these three comboboxes COUNT and fill the report.
|
|
|
|
|
DHARM PAL wrote: how can i add statement to also count number of students in every category..
It's not clear from your example output where that data is coming from.
DHARM PAL wrote: is there a way to add where clause based on column ADstatus and OLstatus AND EXstatus programetically
Not easily with a grouped view, since you presumably want to filter on those columns before grouping the data. You could try using a stored procedure instead.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
thanks sir for helping me.......
i have solve the issue...
thanks again..
expecting help for the next problem....
thanks.....
if u have time please reply
|
|
|
|
|
Hello,
I am developing a simple website where I click on a button and initiate a Remote Desktop Connection to a remote host. The command button will have the hostname of the remote system. How do I start MSTSC.exe and pass it the hostname I want to connect to?
Thanks in advanc,
Rich
|
|
|
|
|
|
I have a dll written for C# and I'm trying to use on my vb.net project.
I add the reference , and also I add the Imports.......
But when I try to call function and methods on VB.net , I get errors. ( on C# project everything works ok )
Just for information , this is an open source dll project in this link : https://github.com/MikaelEliasson/EntityFramework.Utilities
that can be added using NUGET in any visual studio .net applications.
... and I'm trying to use IncludeEFU method.
Any solution will be very helpful.
Thank you !
|
|
|
|
|
...sigh... Why did you leave out the error message from your post? It's only the most important piece of information used to solved the problem.
|
|
|
|
|
Thank you !
From the dll in the link I posted above ( I repeat is an open source and everybody can see the code ) ,
I try to use the "IncludeEFU" method:
In the link I have posted before , is an example that in a C# project works :
var result = db.Contacts
.IncludeEFU(db, x => x.PhoneNumbers
.Where(n => n.Number == "10134")
.OrderBy(p => p.ContactId)
.ThenByDescending(p => p.Number))
.ToList();
I try to use this code on my vb.net project :
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()
But I'm getting thiserror :
An unhandled exception of type 'System.ArgumentException' occurred in EntityFramework.Utilities.dll
Additional information: Could not find a MemberExpression
What is the problem ?
Thank you !
|
|
|
|
|
The error sounds like it's caused by a difference between how C# and VB.NET compile lambda expressions. It sounds like that project hasn't been tested with VB.NET lambdas, and has a bug.
The best place to report problems with that project would be on the project's "issues" page:
https://github.com/MikaelEliasson/EntityFramework.Utilities/issues[^]
In fact, it looks like someone's beaten you to it:
Hello !
I'm using VB.net and entity framework 6.1
I try to use the IncludeEFU.
This is the Code :
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 !
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
These are the codes in that dll related to my method :
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;
}
}
}
}
|
|
|
|
|
And this too :
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; }
}
}
|
|
|
|
|
Do you see any problem in these codes , according to your idea about the possible problem ?
Thank you 1
|
|
|
|
|
Yes, there is a problem with the SetCollectionModifiersAndGetChildProperty method, due to a difference between how C# and VB.NET generate lambda expressions.
As I said, you need to report the bug on the project's "issues" page.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Yes but It seems that this error is reported on the author's website but no response .
Waiting for the response from the author , is there any other way to resolve this problem without changing the .dll code ?
|
|
|
|
|
The issue was reported 21 hours ago.
Some people have full-time jobs to do; they can't be sat by their computer 24/7 waiting to fix problems reported with code they've written in their spare time.
Have a little patience.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Or , if there's a small problem , how can be corrected and we can compile again the DLL since it's public for everyone ?
|
|
|
|
|
First off, I have no clue how to use Excel. I can write a formula, but I'm not the Excel Guru.
I want to embed the pictures in the cells within the file, plus make the picture smaller and center it in the cell.
So I'm using this now, which adds the picture, but I can't resize the picture; so I made the row taller. But I'm not sure if the picture travels with the file.
Any insight would be helpful.
Dim range_image As Range = ws.Range("A" & rC, "A" & rC)
range_image.Select()
Dim pic As Microsoft.Office.Interop.Excel.Pictures = CType(ws.Pictures(oMissing), Microsoft.Office.Interop.Excel.Pictures)
pic.Insert(appData & lvRecords(idx).sItemImage, oMissing)
'pic.Width = range_image.Width
'pic.Height = range_image.Height
And then I have this code, which makes the file size larger, so I'm thinking that the image is embedded. I thought I could add the shape to the cell, but I get a exception.
It didn't like it.
Dim shape As Shape = ws.Shapes.AddPicture(
appData & lvRecords(idx).sItemImage,
Microsoft.Office.Core.MsoTriState.msoFalse,
Microsoft.Office.Core.MsoTriState.msoCTrue,
0,
0,
25,
25
)
ws.cells("A1", "A1") = Shape
|
|
|
|
|
Sounds highly specialized, so you might want to ask it in MSDN's VSTO forum[^] instead. They are monitored regularly with people that only work with VSTO, so chanses for a good answer incenses i'd think.
|
|
|
|
|
Yeah,
I'm finding that out. Well the file generates, looks good. Got the COMM issue with releasing excel solved, so I don't have a 100 copies of it running in the background.
I'll check out that link and ask over there.
Thanks!
|
|
|
|
|
Hello !
I'm using VB.net and entity Framework.
I have situation where depending on several conditions I construct a query step by step .
This is the code :(Article has 2 relationship with F1 and F2)
Dim list1 As IEnumerable(Of Article)
list1 = From t In context.Articles.Include("F1").Include("F2") Order By t.name Ascending
Select t
If condition1 then
list1 = list1.Where(Function(t2) t2.tp = 1)
End If
If condition2 then
list1 = list1.Where(Function(t2) t2.id = 7)
End If
Myreport.datasource=list1.ToList()
This code is working , but now I want to add a new condition , and if this is true the child "F1" should be filtered.
How can I filter the Child "F1"
Thank you !
|
|
|
|
|
|
Hello !
But for my case described in my first post , can you write some code how should I use your idea ?
Thank you !
|
|
|
|
|
Well, list1 is just a list of articles so you can use Linq on the rest:
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
|
|
|
|
|
Hello !
List1 is a list of article , but in my case is a temporary list that change depending to some conditions and at the end the Tolist is called to retrieve data from database.
My problem is not to filter Articles because this is easy , but to filter the child entities F1 and F2.
|
|
|
|
|
Then re-query it?
list1 = From t In context.Articles.Include("F2") Order By t.name Ascending
Select t
|
|
|
|
|