CODEWITHSUNDEEP
c#sql.netlinq
Zwan
Zwan

Reputation: 642

Refactorise Linq query

I have a LINQ query I am trying to minimise, but I get an error when i try. Genkai_db is a Entity Framework instance and i query sql table.

This is working:

public UNITY_DB_PRODEntities12 Genkai_db = new UNITY_DB_PRODEntities12();


    public List<string[]> Query_FpacInactif()
    {
        List<string[]> li = new List<string[]>();
        List<string> u = Genkai_db.final_full_data
            .Where(x => x.FPAC_TimeStamp > 100)
            .Select(x => x.computername)
            .ToList<string>();
        foreach (string a in u)
        {
            string[] Sarray = new string[] { a, "FPAC" };
            li.Add(Sarray);
        }

        return li;
    }

In another similar function I tried to refactor into one line:

public List<string[]> Query_McAfeeConsolidation()
{
    List<string[]> li = (Genkai_db.Consolidation_McAfee
        .Select(x => new string[] { x.computername, "McAfee" }))
        .ToList();
    return li;  
}

This last function failed with error:

Unable to initialize the array of type 'System.String []' in a query result. Use 'System.Collections.Generic.List`1 [System.String]' instead.

PS: I tried a lot of things before asking even using List<string> over string[] but nothing has worked in one line.

Upvotes: 2

Views: 101

Answers (3)

Rahul Singh
Rahul Singh

Reputation: 21795

I guess it is not able to translate it into SQL query, you can bring it back in memory using AsEnumerable and project.

Based on your original query you can always filter the records in SQL, pervious query loads entire data in memory which is not good obviously (but wrote that based on your Query_McAfeeConsolidation method):-

 List<string[]> li = Genkai_db.Consolidation_McAfee
                          .Where(x => x.FPAC_TimeStamp > 100)
                          .Select(x => x.computername)
                          .AsEnumerable()
                          .Select(x => new string[] { x, "McAfee" }))
                          .ToList();

Upvotes: 4

juharr
juharr

Reputation: 32266

This should work

return Genkai_db.final_full_data
    .Where(x => x.FPAC_TimeStamp > 100)
    .Select(x => x.computername)
    .AsEnumerable()
    .Select(cn => new string[] { cn, "FPAC" })
    .ToList();

This will query the computername from the DB and then create the array in memory because the AsEnumerbale will transition from EF to Linq-to-objects.

Upvotes: 3

user1023602
user1023602

Reputation:

Linq doesn't seem to work well with arrays.

Use List<List<string>> instead:

  public List<List<string>>> Query_McAfeeConsolidation()
  {
      List<List<string>> li = Genkai_db.Consolidation_McAfee
                                       .Select(x => new List<string> { x.computername, "McAfee" })
                                       .ToList();
      return li;
  }

Upvotes: 0

Related Questions