Solved

Linq. Get Data By Index

Posted on 2014-03-03
43
932 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 39

Expert Comment

by:Kyle Abrahams
Comment Utility
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
Comment Utility
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 39

Expert Comment

by:Kyle Abrahams
Comment Utility
In your example t knows what columns it has.  It's a list of (t) which is defined in your entity framework.
0
 
LVL 74

Expert Comment

by:käµfm³d 👽
Comment Utility
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
Comment Utility
Kyle. If t knows, is there a way to access using a number?
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
Comment Utility
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
Comment Utility
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 39

Expert Comment

by:Kyle Abrahams
Comment Utility
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
Comment Utility
"pull the entity name out of the class and use that instead" ?

How would I do that ?
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
Comment Utility
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
Comment Utility
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 39

Expert Comment

by:Kyle Abrahams
Comment Utility
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
Comment Utility
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 39

Expert Comment

by:Kyle Abrahams
Comment Utility
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
Comment Utility
hmm.  What do I put in place of myclass ?
0
 
LVL 1

Author Comment

by:Dodsworth
Comment Utility
Kyle?
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
Comment Utility
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
Comment Utility
L is a linq query definition tho ?

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

Expert Comment

by:Kyle Abrahams
Comment Utility
right but what type is LU?
0
 
LVL 1

Author Comment

by:Dodsworth
Comment Utility
Isn't LU an alias for dc.Lookup ?
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
Comment Utility
but lookup is also the type in that case . . . so you would use that for the class.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 1

Author Comment

by:Dodsworth
Comment Utility
I tried.. not liking.. LookUp is a Type and cannot be used in an expression.
0
 
LVL 39

Expert Comment

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

Author Comment

by:Dodsworth
Comment Utility
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
Comment Utility
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 39

Expert Comment

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

Author Comment

by:Dodsworth
Comment Utility
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 39

Expert Comment

by:Kyle Abrahams
Comment Utility
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
Comment Utility
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 39

Expert Comment

by:Kyle Abrahams
Comment Utility
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
Comment Utility
It still doesn't like the p.name bit :(
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
Comment Utility
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
Comment Utility
OK will do. Thanks for you patience !
0
 
LVL 1

Author Comment

by:Dodsworth
Comment Utility
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 39

Expert Comment

by:Kyle Abrahams
Comment Utility
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
Comment Utility
0
 
LVL 1

Author Comment

by:Dodsworth
Comment Utility
any help ?
0
 
LVL 39

Expert Comment

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

Author Comment

by:Dodsworth
Comment Utility
:)
0
 
LVL 39

Accepted Solution

by:
Kyle Abrahams earned 500 total points
Comment Utility
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 39

Expert Comment

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

Author Comment

by:Dodsworth
Comment Utility
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 39

Expert Comment

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

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
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…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

744 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now