Solved

Linq. Get Data By Index

Posted on 2014-03-03
43
967 Views
Last Modified: 2016-02-10
Not the best of titles as it's difficult to explain...

I can use
Dim query = From t in dc.myTable
Dim myText as String = query.ToList.ElementEt(0).AuditText

Open in new window

to get a string from the AuditText column of the first row of myTable.

But how to select the required column using an index rather than an explicitly named column ?

Pseudo...
Dim myText as String = query.ToList.elementat(0).Columns(2)

Open in new window

0
Comment
Question by:Dodsworth
  • 22
  • 20
43 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39901002
try:
query.ToList.elementat(0)(2)

Why wouldn't you want to use the ColumnName though?  The whole point is that you grab the data you need.  If you rearrange your columns later, the index would be off.
0
 
LVL 1

Author Comment

by:Dodsworth
ID: 39901041
That gives "Class cannot be indexed because it has no default property."

I need to use an index because the calling code does not 'know' the column names in the query.
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39901070
In your example t knows what columns it has.  It's a list of (t) which is defined in your entity framework.
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 39901096
You could define a default property on your class which returns the values by index. However, this is a very brittle design, as if the columns you select ever change, then you will need to change the default property to match. The following provides information on defining a default property:

http://msdn.microsoft.com/en-us/library/se6z814t.aspx
http://msdn.microsoft.com/en-us/library/az06zx4y(v=vs.90).aspx
http://visualbasic.about.com/od/quicktips/qt/defprop.htm

In your case, you would just have a Select Case that returned the appropriate column's value based on the index passed in.

If you go this route, then ged325's syntax (http:#a39901002) works.
0
 
LVL 1

Author Comment

by:Dodsworth
ID: 39901310
Kyle. If t knows, is there a way to access using a number?
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39901346
As Kaufmed stated the answer lies in setting a default property on the class.  

I completely agree that's it's a VERY brittle design and can break.  You're much better off using the column name as it's defined for you.
0
 
LVL 1

Author Comment

by:Dodsworth
ID: 39901386
It's not production code.  I'm using it to generate code.  The source will always be a straight entity class. (if that makes any difference:)
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39901645
so if you're using it to generate code . . . pull the entity name out of the class and use that instead.

If you post some code maybe we can help further?
0
 
LVL 1

Author Comment

by:Dodsworth
ID: 39902728
"pull the entity name out of the class and use that instead" ?

How would I do that ?
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39903422
from: http://handcraftsman.wordpress.com/2008/11/11/how-to-get-c-property-names-without-magic-strings/

public static string GetPropertyName<T>(Expression<Func<T>> expression)
	{
		MemberExpression body = (MemberExpression) expression.Body;
		return body.Member.Name;
	}

Open in new window


Or:

foreach (var p in myClass.GetType().GetProperties())
{
   MsgBox(String.Format("{0} is {1}", p.Name. p.GetValue(myClass, null)));
}

Open in new window

0
 
LVL 1

Author Comment

by:Dodsworth
ID: 39903690
Ok here is the static code that I'm using to set a ListPicker to the same value as a Linq query:

lp.SelectedIndex = l.ToList.FindIndex(Function(x) x.LookUpText = query.FirstOrDefault.AuditType)

Open in new window


This is good to look up AuditTypes, but I want it to adapt to so that it works for any kind of look up.  The code preceding it 'knows' the column that we want to filter on (AuditType in this example) but it only 'knows' the column as a string or a number.

Hope that makes a little more sense of what I'm trying to do.
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39906700
I'm posting the C# and will try to translate:

        List<myCLass> l = new List<myCLass>();
        l.FindIndex(x=> x.GetType().GetProperties().Where(p =>p.Name == prop).Single().GetValue(myCLass, null).ToString() == <someVal or Something>);


in your example you have the value first . . . no issues there.

lp.SelectedIndex = l.ToList.FindIndex(Function(x) x.LookUpText =
'go to the query, then get the properties of the query.  Match on the string value.  query.FirstOrDefault.GetType.GetProperties.Where(Function(p) p.Name = STRING_PROPERTY_NAME))
0
 
LVL 1

Author Comment

by:Dodsworth
ID: 39909196
That looked promising, but I get the error

{System.InvalidCastException: Unable to cast object of type 'WhereArrayIterator`1[System.Reflection.PropertyInfo]' to type 'System.String'.     at Tag.Entry._Closure$__1._Closure$__2._Lambda$__1(LookUp x)     at System.Collections.Generic.List`1.FindIndex(Int32 startIndex, Int32 count, Predicate`1 match)     at System.Collections.Generic.List`1.FindIndex(Predicate`1 match)     at Tag.Entry.getData()}
    System.InvalidCastException: {System.InvalidCastException: Unable to cast object of type 'WhereArrayIterator`1[System.Reflection.PropertyInfo]' to type 'System.String'.     at Tag.Entry._Closure$__1._Closure$__2._Lambda$__1(LookUp x)     at System.Collections.Generic.List`1.FindIndex(Int32 startIndex, Int32 count, Predicate`1 match)     at System.Collections.Generic.List`1.FindIndex(Predicate`1 match)     at Tag.Entry.getData()}
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39909524
Makes sense . . . you have to bring back the value of the property, not the property itself.

I threw in the tolower on each side as VB is case insensitive.  Feel free to take it out if you want a more exact match.

lp.SelectedIndex = l.ToList.FindIndex(Function(x) x.LookUpText =
query.FirstOrDefault.GetType.GetProperties.Where(Function(p) p.Name.ToLower = STRING_PROPERTY_NAME.ToLower())) .Single.GetValue(myclass, null)
)
0
 
LVL 1

Author Comment

by:Dodsworth
ID: 39914601
hmm.  What do I put in place of myclass ?
0
 
LVL 1

Author Comment

by:Dodsworth
ID: 39918247
Kyle?
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39923715
Sorry lost track of this.  (ran off the first page)

My class would be the class of whatever L is.
0
 
LVL 1

Author Comment

by:Dodsworth
ID: 39923786
L is a linq query definition tho ?

Dim l = From lu In dc.LookUp Where lu.LookupType.Equals(col.column.Substring(1))
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39923868
right but what type is LU?
0
 
LVL 1

Author Comment

by:Dodsworth
ID: 39923894
Isn't LU an alias for dc.Lookup ?
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39926775
but lookup is also the type in that case . . . so you would use that for the class.
0
 
LVL 1

Author Comment

by:Dodsworth
ID: 39931805
I tried.. not liking.. LookUp is a Type and cannot be used in an expression.
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39934452
Can you post your full statement from when you were getting the InvalidCastException error?
0
 
LVL 1

Author Comment

by:Dodsworth
ID: 39934557
lp.SelectedIndex = l.ToList.FindIndex(Function(x) x.LookUpText = query.FirstOrDefault.GetType.GetProperties.Where(Function(p) p.Name = "_AuditType"))

Open in new window

0
 
LVL 1

Author Comment

by:Dodsworth
ID: 39934669
I think the problem may be around p.Name.

If I debug..

?query.firstordefault.gettype.GetProperties
{Length=3}
    (0): {Int32 AuditId}
    (1): {System.String _AuditType}
    (2): {System.Nullable`1[System.Int32] GroupId}

but if I try..

?query.firstordefault.gettype.GetProperty("Name")
Nothing
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39934771
Try
?query.firstordefault.gettype.GetProperties()(0).Name
0
 
LVL 1

Author Comment

by:Dodsworth
ID: 39934809
Yes that gets the name OK (after I supplied the correct column (which I have already)) How to put into the function tho?
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39934919
To Get the property:

query.firstordefault.gettype.GetProperties().Where( Function(p) ( p.name.ToLower() = "MyProperty")

To Get the value of that property

query.firstordefault.gettype.GetProperties().Where( Function(p) ( p.name.ToLower() = "MyProperty").Single().GetValue(query.firstordefault, nothing)
0
 
LVL 1

Author Comment

by:Dodsworth
ID: 39936292
query.firstordefault.gettype.GetProperties().Where( Function(p) ( p.name.ToLower() = "MyProperty").Single().GetValue(query.firstordefault, nothing) )

Gives..

Single is not a member of boolean
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39937841
Sorry used to C# . . . missed a paren:

query.firstordefault.gettype.GetProperties().Where(
 Function(p) ( p.name.ToLower() = "MyProperty")
).Single().GetValue(query.firstordefault, nothing)
0
 
LVL 1

Author Comment

by:Dodsworth
ID: 39945472
It still doesn't like the p.name bit :(
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39945755
can you post your actual code?

the lines and any variable declarations with sample data referenced in those lines.
0
 
LVL 1

Author Comment

by:Dodsworth
ID: 39945798
OK will do. Thanks for you patience !
0
 
LVL 1

Author Comment

by:Dodsworth
ID: 39947753
I hope this is usable. I tried uploading a zip of the project but there were so many problems with file types not being allowed.
MainPage.xaml
MainPage.xaml.vb
startcontext.vb
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39950570
I'm not seeing your audit types . . .
I need to see the area where you're trying to call by index or name?  

Maybe create a smaller sample project with just the bare necessities?  

And you may need to upload to a different site and point us at the link.
0
 
LVL 1

Author Comment

by:Dodsworth
ID: 39950868
0
 
LVL 1

Author Comment

by:Dodsworth
ID: 39956131
any help ?
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39956217
I have the project but haven't had a chance to look at it yet. Stay tuned.
0
 
LVL 1

Author Comment

by:Dodsworth
ID: 39956684
:)
0
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 500 total points
ID: 39960414
I can't open the project because I don't have silverlight here, but looking around the code I didn't see where you were trying to do the lookup.  I have a VB project here that I ran so this is what the code should look like:

 'ClsNotes has the following properties:
        'string Note
        'create Date
        'string created_by
        'int Foreign_ID
        'int NoteID

        'query can be a list, a var, doesn't matter
        Dim query As New List(Of clsNotes)

        'I'm creating to objects, to populate the list, can come from a LINQ query.
        Dim c As New clsNotes("", DateTime.Now, "me", -1, 1)
        Dim d As New clsNotes("", DateTime.Now, "me2", -1, 2)

        query.Add(c)
        query.Add(d)


        'the property I'm looking for.
        Dim propertyName As String = "created_by"


        Dim i As Integer


        'take the list
        'get the value of the property in the class where the property name = the property I'm looking for.
        'if the value is the value I'm looking for (in your case this could be x.LookUpText), return it
        'I'm currently only lookign for one object, but this could be another list by using ToList() instead of single

        Dim somethingElse As clsNotes = query.Where(            
                            Function(x)
                                Dim val As String = x.GetType().GetProperties().Where(Function(p) p.Name.ToLower = propertyName.ToLower).Single.GetValue(x, Nothing).ToString()
                                If val = "me2" Then
                                    Return True
                                End If

                            End Function).Single

        'Get the value of something else in the class
        i = somethingElse.NoteID


        'write it out.
        Response.Write(i.ToString)

Open in new window


Note that you can set a break point on the val = inside the linq query to see it actually running and doing the compare.  Let me know if you have any issues.
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39972692
Just following up to see if you need any more help.
0
 
LVL 1

Author Comment

by:Dodsworth
ID: 39972740
Hi sorry.. A bit busy to look into it at the mo.  No landline for a week and no mobile signal here so, I've been emailing people all week!  Will check back when the phones are fixed :)
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39975142
Post back when you try it.  Going to leave this as my comment so something will flag.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question